package com.base.controller.api;

import com.base.comm.CommUrls;
import com.base.controller.Parameters;
import com.base.helper.ApiinfoesHelper;
import com.base.helper.VosHelper;
import com.base.po.czb.GasOrderH5read;
import com.base.po.param.InfoCustomers;
import com.base.po.param.NetState;
import com.base.po.param.VosModelList;
import com.base.po.sqlparam.OperationlogsExamplePage;
import com.base.po.sqlparam.PayinfoesExamplePage;
import com.base.po.sqlparam.RechargeablecardsExamplePage;
import com.base.po.voip.*;
import com.base.po.voip.RechargeablecardsExample.Criteria;
import com.base.po.voip_sdk.AddressBookContext;
import com.base.po.voip_sdk.BookContext;
import com.base.service.*;
import com.base.service.CZB.GasOrderH5readService;
import com.base.utils.DateUtil;
import com.base.utils.GsonUtil;
import com.base.utils.Page;
import com.base.utils.Produce;
import com.beust.jcommander.internal.Lists;
import net.sf.json.JSONArray;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;
import org.apache.commons.collections.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/apiTest")
public class TestController {
    @Autowired
    private RechargeablecardsService rechargeablecardsService;
    @Autowired
    private RechargeablecardsGiftService rechargeablecardsGiftService;
    @Autowired
    private SubscribersService subscribersService;
    @Autowired
    private UsersService usersService;
    @Autowired
    private ApiinfoesService apiinfoesService;
    @Autowired
    private PayinfoesService payinfoesService;
    @Autowired
    private CallbackinfoesService callbackinfoesService;
    @Autowired
    private SmsconfigsService smsconfigsService;
    @Autowired
    private SelfShopOrderService selfShopOrderService;
    @Autowired
    private SelfShopGoodsService selfShopGoodsService;
    @Autowired
    private GasOrderH5readService gasOrderH5readService;
    @Autowired
    private LogsService logsService;
    @Autowired
    private TrafficCardApplicationService trafficCardApplicationService;
    @Autowired
    private AddressBookService addressBookService;
    @Autowired
    private SelfShopService selfShopService;
    @Autowired
    private SelfShopWithdrawalsService selfShopWithdrawalsService;

    //读取配置文件
    @Value("${system.file-path}")
    private String filePath;

    @SuppressWarnings("resource")
    @RequestMapping(value = "/export", method = RequestMethod.POST)
    public void export(HttpServletRequest request, HttpServletResponse response, Integer money, // 面值
                       String adminUserId, // 操作用户的id
                       String parentid, // 代理商
                       String mobile, // 充值号码
                       Integer status, // 充值卡状态
                       String batch, // 批次
                       String cardnoFrom, String cardnoTo) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        RechargeablecardsExamplePage examplePage = new RechargeablecardsExamplePage();
        examplePage.setOrderByClause("CardNo");
        List<Rechargeablecards> rechargeablecards = null;
        Criteria criteria = examplePage.createCriteria();
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        Integer choose = 1;
        // 如果choose为1 条件查询 choose为2 查询所有
        if (cardnoFrom != null && !cardnoFrom.equals("") && cardnoTo != null && !cardnoTo.equals("") || money != null
                || mobile != null && !mobile.equals("") || parentid != null && !parentid.equals("")
                || batch != null && !batch.equals("")) {
            choose = 1;
        } else {
            choose = 2;
            out.write("请根据批号/面值/代理商/卡号段/充值号码,导出信息");
            return;
        }

//		if (mine.getRole() == 0) {
//			// 条件筛选
//			if (cardnoFrom != null && !cardnoFrom.equals("") && cardnoTo != null && !cardnoTo.equals("")) {
//				cardnoFrom = cardnoFrom.trim();
//				cardnoTo = cardnoTo.trim();
//				criteria = criteria.andCardnoBetween(cardnoFrom, cardnoTo);
//				examplePage.getOredCriteria().add(criteria);
//			}
//			if (money != null) {
//				criteria = criteria.andMoneyEqualTo(money);
//				examplePage.getOredCriteria().add(criteria);
//			}
//			if (mobile != null && !mobile.equals("")) {
//				criteria = criteria.andMobileEqualTo(mobile);
//				examplePage.getOredCriteria().add(criteria);
//			}
//			if (parentid != null && !parentid.equals("")) {
//				criteria = criteria.andParentidEqualTo(usersService.selectByUsername(parentid).getId());
//				examplePage.getOredCriteria().add(criteria);
//			}
//			if (batch != null && !batch.equals("")) {
//				batch = batch.trim();
//				criteria = criteria.andBatchEqualTo(batch);
//				examplePage.getOredCriteria().add(criteria);
//			}
//			if (status != null) {
//				// 判断当前可用性
//				switch (status) {
//				case 0:
//					examplePage.getOredCriteria().add(criteria.andStatusEqualTo(0));
//					break;
//				case 1:
//					examplePage.getOredCriteria().add(criteria.andStatusEqualTo(1));
//					break;
//				case 2:
//					break;
//				}
//			}
//		}
//		/**
//		 * TODO 查询一级oem和一级商家的卡
//		 */
//		if (mine.getRole() == 1) {
//			List<String> list = usersService.selectByParentId(adminUserId);
//			if (cardnoFrom != null && !cardnoFrom.equals("") && cardnoTo != null && !cardnoTo.equals("")) {
//				cardnoFrom = cardnoFrom.trim();
//				cardnoTo = cardnoTo.trim();
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andCardnoBetween(cardnoFrom,
//							cardnoTo));
//
//				}
//				examplePage.getOredCriteria()
//						.add(criteria.andParentidEqualTo(adminUserId).andCardnoBetween(cardnoFrom, cardnoTo));
//			}
//			if (money != null) {
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andMoneyEqualTo(money));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andMoneyEqualTo(money));
//			}
//			if (mobile != null && !mobile.equals("")) {
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andMobileEqualTo(mobile));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andMobileEqualTo(mobile));
//			}
//			if (parentid != null && !parentid.equals("")) {
//				Users users = usersService.selectByUsername(parentid);
//				//TODO
////				for (String oemId : list) {
////					// OEM下的商家
////					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId)
////							.andParentidEqualTo(users.getParentid()));
////				}
//				examplePage.getOredCriteria()
//						.add(criteria.andParentidEqualTo(users.getId()));
//			}
//			if (batch != null && !batch.equals("")) {
//				batch = batch.trim();
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andBatchEqualTo(batch));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andBatchEqualTo(batch));
//			}
//			if (status != null) {
//				// 判断当前可用性
//				switch (status) {
//				case 0:
//					for (String oemId : list) {
//						// OEM下的商家
//						examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andStatusEqualTo(0));
//					}
//					examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andStatusEqualTo(0));
//					break;
//				case 1:
//					for (String oemId : list) {
//						// OEM下的商家
//						examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andStatusEqualTo(1));
//					}
//					examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andStatusEqualTo(1));
//					break;
//				case 2:
//					break;
//				}
//			}
//		}
//
//		if (mine.getRole() == 2) {
//			List<String> list = usersService.selectByShopId(adminUserId);
//			if (cardnoFrom != null && !cardnoFrom.equals("") && cardnoTo != null && !cardnoTo.equals("")) {
//				cardnoFrom = cardnoFrom.trim();
//				cardnoTo = cardnoTo.trim();
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andCardnoBetween(cardnoFrom,
//							cardnoTo));
//
//				}
//				examplePage.getOredCriteria()
//						.add(criteria.andParentidEqualTo(adminUserId).andCardnoBetween(cardnoFrom, cardnoTo));
//			}
//			if (money != null) {
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andMoneyEqualTo(money));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andMoneyEqualTo(money));
//			}
//			if (mobile != null && !mobile.equals("")) {
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andMobileEqualTo(mobile));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andMobileEqualTo(mobile));
//			}
//			if (parentid != null && !parentid.equals("")) {
//				Users users = usersService.selectByUsername(parentid);
//				//TODO
////				for (String oemId : list) {
////					// OEM下的商家
////					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId)
////							.andParentidEqualTo(users.getParentid()));
////				}
//				examplePage.getOredCriteria()
//						.add(criteria.andMobileEqualTo(users.getId()));
//			}
//			if (batch != null && !batch.equals("")) {
//				batch = batch.trim();
//				for (String oemId : list) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andBatchEqualTo(batch));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andBatchEqualTo(batch));
//			}
//			if (status != null) {
//				// 判断当前可用性
//				switch (status) {
//				case 0:
//					for (String oemId : list) {
//						// OEM下的商家
//						examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andStatusEqualTo(0));
//					}
//					examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andStatusEqualTo(0));
//					break;
//				case 1:
//					for (String oemId : list) {
//						// OEM下的商家
//						examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId).andStatusEqualTo(1));
//					}
//					examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId).andStatusEqualTo(1));
//					break;
//				case 2:
//					break;
//				}
//			}
//		}
//		if (choose == 2) {
//			switch (mine.getRole()) {
//			case 0:
//				break;
//
//			case 1:
//				// 查询OEMID下的用户
//				// 查询OEM下的商户以及OEM
//
//				List<String> l = usersService.selectByParentId(adminUserId);
//				System.out.println("Users的数量：" + l.size());
//				for (String oemId : l) {
//					// OEM下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId));
//				break;
//
//			case 2:
//				List<String> lists = usersService.selectByParentId(adminUserId);
//				System.out.println("Users的数量：" + lists.size());
//				for (String shopId : lists) {
//					// 商家下的商家
//					examplePage.or(examplePage.createCriteria().andParentidEqualTo(shopId));
//				}
//				examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId));
//				break;
//			}
//		}

        String sCardno = null;
        Integer sMoney = null;// 面值
        String pId = null; // 代理商
        String sMobile = null;// 充值号码
        Integer sStatus = null;// 充值卡状态
        Integer sTermofvalidity = null; // 有效期
        Integer sProbationperiod = null;// 使用有效期
        String sBatch = null;  // 批次
        Boolean sDatastatus = null;// 冻结结果
        String sCardnoFrom = null;
        String sCardnoTo = null;
        Integer sType = null;
        String sFeelroute = null;

        if (cardnoFrom != null && !cardnoFrom.equals("") && cardnoTo != null && !cardnoTo.equals("")) {
            sCardnoFrom = cardnoFrom;
            sCardnoTo = cardnoTo;
        }
        if (money != null) {
            sMoney = money;
        }
        if (mobile != null && !mobile.equals("")) {
            sMobile = mobile;
        }
        if (batch != null && !batch.equals("")) {
            sBatch = batch;
        }
        //TODO admin 根据是否充值过查询
        if (status != null) {
            // 判断当前可用性
            switch (status) {
                case 0:

                    break;
                case 1:
                    //未充值
                    sStatus = 0;
                    break;
                case 2:
                    //已充值
                    sStatus = 1;
                    break;
                case 3:

                    break;
            }
        }

        // 判断当前可用性
        if (parentid != null && !parentid.equals("")) {
            Users users = usersService.selectByUsername(parentid);
            pId = users.getId();
        }
        List<String> parentIds = Lists.newArrayList();
        Page page = new Page();
        // admin条件筛选
        page.setCurrentPageNo(1);
//		if (mine.getRole() == 0) {
//			if (parentIds.size() == 0) {
//				parentIds = null;
//			}
//			Integer allNum=rechargeablecardsService.countByAllNew(sStatus, sBatch, sMoney, sMobile, parentIds, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
//			page.setRecordTotal(allNum);
//			page.setPageSize(allNum);
//			rechargeablecards = rechargeablecardsService.selectByAllNew(page, sStatus, sBatch, sMoney, sMobile, parentIds, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
//		}
//		if(mine.getRole() == 1) {
//			page.setRecordTotal(rechargeablecardsService.countByParentid(adminUserId,sStatus, sBatch, sMoney, sMobile, pId, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute));
//			rechargeablecards = rechargeablecardsService.selectByParentid(page,adminUserId,sStatus, sBatch, sMoney, sMobile, pId, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
//		}
//        if(mine.getRole() == 2) {
//        	page.setRecordTotal(rechargeablecardsService.countByShopidNotAll(adminUserId,sStatus, sBatch, sMoney, sMobile, pId, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute));
//			rechargeablecards = rechargeablecardsService.selectByShopidNotAll(page,adminUserId,sStatus, sBatch, sMoney, sMobile, pId, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
//		}
//
//		if (choose == 2) {
//			switch (mine.getRole()) {
//			case 0:
//				page.setCurrentPageNo(1);
//				page.setRecordTotal(rechargeablecardsService.countByAll(null, null, null, null, null, null, null, null, null, null, null, null, null));
//				rechargeablecards = rechargeablecardsService.selectByAll(page, null, null, null, null, null, null, null, null, null, null, null, null, null);
//				break;
//			case 1:
//				page.setCurrentPageNo(1);
//				page.setRecordTotal(rechargeablecardsService.countByParentid(adminUserId,null, null,null, null, null, null, null, null, null, null, null, null, null));
//				rechargeablecards = rechargeablecardsService.selectByParentid(page,adminUserId, null, null, null, null, null, null, null, null, null, null, null, null, null);
//				break;
//			case 2:
//				page.setCurrentPageNo(1);
//				page.setRecordTotal(rechargeablecardsService.countByShopidNotAll(adminUserId,null, null,null, null, null, null, null, null, null, null, null, null, null));
//				rechargeablecards = rechargeablecardsService.selectByShopidNotAll(page,adminUserId, null, null, null, null, null, null, null, null, null, null, null, null, null);
//				break;
//			}
//		}
//
        
        /**
         * TODO 开始导出卡信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出卡<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//		int totlenum = rechargeablecardsService.countByExample(examplePage);// 页面总数
        int totlenum = page.getRecordTotal();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        int times = totlenum / 10000;
        int end = 0;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万张卡数据");
            if (rechargeablecards == null) {
                rechargeablecards = new ArrayList<Rechargeablecards>();
            } else {
                rechargeablecards.clear();
            }
//			examplePage.start = start;
//			examplePage.rows = 10000;// 设置首次分页
            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);
//			rechargeablecards = rechargeablecardsService.selectByExamplePage(examplePage);
            if (mine.getRole() == 0) {
                if (parentIds.size() == 0) {
                    parentIds = null;
                }
                rechargeablecards = rechargeablecardsService.selectByAllNew(page, sStatus, sBatch, sMoney, sMobile, parentIds, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
            }
            if (mine.getRole() == 1) {
                rechargeablecards = rechargeablecardsService.selectByParentid(page, adminUserId, sStatus, sBatch, sMoney, sMobile, pId, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
            }
            if (mine.getRole() == 2) {
                rechargeablecards = rechargeablecardsService.selectByShopidNotAll(page, adminUserId, sStatus, sBatch, sMoney, sMobile, pId, sCardno, sTermofvalidity, sProbationperiod, sCardnoFrom, sCardnoTo, sDatastatus, sType, sFeelroute);
            }
            
            /**
             *  @Date: 2020/6/28 19:00
             *  @Description:
             *  排序一下
             */
            Collections.sort(rechargeablecards, new Comparator<Rechargeablecards>() {
                @Override
                public int compare(Rechargeablecards o1, Rechargeablecards o2) {
                    int i = new BigDecimal(o1.getCardno()).compareTo(new BigDecimal(o2.getCardno()));
                    return i;
                }
            });
            System.out.println(">>>完成查询第" + (start + 1) + "万张卡数据的查找，共" + rechargeablecards.size() + "张卡数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万张充值卡");
            end = start + 1;
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("编号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("卡号");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("卡密");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("面值(单位:元)");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("购物金");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("是否被充值");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("有效期（单位:天）");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("批次号");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("开卡时间");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("过期时间");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("充值卡类型");
            HSSFCell tc11 = title.createCell(11);
            tc11.setCellValue("被充值手机号");
            for (int i = 0; i < rechargeablecards.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(rechargeablecards.get(i).getNumber());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(rechargeablecards.get(i).getCardno());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(rechargeablecards.get(i).getPwd());
                HSSFCell titlecell3 = row.createCell(3);
                if (rechargeablecards.get(i).getType() == 5) {
                    titlecell3.setCellValue(0);
                } else {
                    titlecell3.setCellValue(rechargeablecards.get(i).getMoney());
                }
                HSSFCell titlecell4 = row.createCell(4);
                if (rechargeablecards.get(i).getType() == 5 || rechargeablecards.get(i).getType() == 6) {
                    titlecell4.setCellValue(rechargeablecards.get(i).getMoney());
                } else if (rechargeablecards.get(i).getType() == 7) {
                    titlecell4.setCellValue(rechargeablecardsGiftService.selectByBatch(rechargeablecards.get(i).getBatch()) == null
                            ? 0 : rechargeablecardsGiftService.selectByBatch(rechargeablecards.get(i).getBatch()).getShopmoney());
                } else {
                    titlecell4.setCellValue("0");
                }
                HSSFCell titlecell5 = row.createCell(5);
                if (rechargeablecards.get(i).getStatus() != 0) {
                    titlecell5.setCellValue("已充值");
                }
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(rechargeablecards.get(i).getTermofvalidity() + " 天");
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(rechargeablecards.get(i).getBatch());
                HSSFCell titlecell8 = row.createCell(8);
                //TODO 开卡时间
                titlecell8.setCellValue(format.format(rechargeablecards.get(i).getCreatetime()));
                HSSFCell titlecell9 = row.createCell(9);
                //TODO 过期时间
                titlecell9.setCellValue(format.format(Produce.plusDay(rechargeablecards.get(i).getTermofvalidity(),
                        rechargeablecards.get(i).getCreatetime())));
                HSSFCell titlecell10 = row.createCell(10);
                if (rechargeablecards.get(i).getType() == 1) {
                    titlecell10.setCellValue("月卡");
                } else if (rechargeablecards.get(i).getType() == 2) {
                    titlecell10.setCellValue("季卡");
                } else if (rechargeablecards.get(i).getType() == 3) {
                    titlecell10.setCellValue("年卡");
                } else if (rechargeablecards.get(i).getType() == 4) {
                    titlecell10.setCellValue("面值卡");
                } else if (rechargeablecards.get(i).getType() == 5) {
                    titlecell10.setCellValue("购物卡");
                } else if (rechargeablecards.get(i).getType() == 6) {
                    titlecell10.setCellValue("等值卡");
                } else if (rechargeablecards.get(i).getType() == 7) {
                    titlecell10.setCellValue("自定义卡");
                }

                HSSFCell titlecell11 = row.createCell(11);
                if (rechargeablecards.get(i).getStatus() != 0) {
                    titlecell11.setCellValue(rechargeablecards.get(i).getMobile());
                }
            }
            System.out.println((start + 1) + "万张卡完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        if (batch != null && batch != "") {
            file = new File(filePath+"Excel/批次号" + batch + "充值卡.xls");
        }
        if (mobile != null && mobile != "") {
            file = new File(filePath+"Excel/手机号" + mobile + "充值卡.xls");
        }
        if (cardnoFrom != null && cardnoFrom != "" && cardnoTo != null && cardnoTo != "") {
            file = new File(filePath+"Excel/卡号" + cardnoFrom + "到" + cardnoTo + "充值卡.xls");
        }
        if (parentid != null && parentid != "") {
            file = new File(filePath+"Excel/所属商" + parentid + "充值卡.xls");
        }
        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出卡信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = null;
        if (batch != null && batch != "") {
            fileName = basePath + "file/Excel/批次号" + batch + "充值卡.xls";
        }
        if (mobile != null && mobile != "") {
            fileName = basePath + "file/Excel/手机号" + mobile + "充值卡.xls";
        }
        if (cardnoFrom != null && cardnoFrom != "" && cardnoTo != null && cardnoTo != "") {
            fileName = basePath + "file/Excel/卡号" + cardnoFrom + "到" + cardnoTo + "充值卡.xls";
        }
        if (parentid != null && parentid != "") {
            fileName = basePath + "file/Excel/所属商" + parentid + "充值卡.xls";
        }

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @RequestMapping(value = "/uploadExcel", method = RequestMethod.POST)
    public @ResponseBody
    NetState uploadExcel(HttpServletRequest request, @RequestParam("file") MultipartFile file)
            throws Exception {

        NetState netState = new NetState();
        // 如果文件不为空，写入上传路径
        if (!file.isEmpty()) {
            // 上传文件路径
            // 上传文件名
            String filename = Produce.getUUID() + file.getOriginalFilename();
            String fileReal = CommUrls.FILEPATH + "Excel";
            File filepath = new File(fileReal, filename);
            // 判断路径是否存在，如果不存在就创建一个
            if (!filepath.getParentFile().exists()) {
                filepath.getParentFile().mkdirs();
            }
            // 将上传文件保存到一个目标文件当中
            file.transferTo(new File(fileReal + File.separator + filename));

//			file.transferTo(new File(fileReal + "/" + filename));
            netState.setErrorCode(NetState.SUCCESS_CONN);
            netState.setData("上传文件成功");

//			netState.setJson(fileReal + File.separator + filename);

            netState.setJson(fileReal + "/" + filename);
        } else {
            netState.setErrorCode(NetState.SYSTEM_ERROR);
            netState.setData("上传文件失败");
        }
        Apiinfoes apiinfoes = ApiinfoesHelper.createApiinfoes(request, "上传文件");
        apiinfoesService.insert(ApiinfoesHelper.setApiinfoes(apiinfoes, null, GsonUtil.createJsonString(netState)));
        return netState;

    }

    /**
     * 用户导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/subscribersExport", method = RequestMethod.POST)
    public void subscribersExport(HttpServletRequest request, HttpServletResponse response,
                                  String adminUserId, // 操作用户的id
                                  String username, // 商家名称
                                  String parentid, // 所属代理商
                                  String shopid, // 所属商家
                                  Integer datastatus,
                                  String startTimeFrom, String startTimeTo,
                                  String endTimeFrom, String endTimeTo) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<Subscribers> users = null;
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        if (parentid.equals("undefined")) {
            parentid = null;
        }
        if (shopid.equals("undefined")) {
            shopid = null;
        }

        Integer choose = 1;
        // 如果choose为1 条件查询 choose为2 查询所有
        if (startTimeFrom != null && !startTimeFrom.equals("") && startTimeTo != null && !startTimeTo.equals("")
                || endTimeFrom != null && !endTimeFrom.equals("") && endTimeTo != null && !endTimeTo.equals("")
                || username != null && !username.equals("") || parentid != null && !parentid.equals("")
                || shopid != null && !shopid.equals("")) {
            choose = 1;
        } else {
            choose = 2;
            out.write("请根据查询信息,导出信息");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String pName = null;
        String sName = null;
        String mobile = null;
        String sUserName = null;
        Boolean status = null;
        String sStartTimeFrom = null;
        String sStartTimeTo = null;
        String sEndTimeFrom = null;
        String sEndTimeTo = null;
        Page page = new Page();

        if (mine.getRole() == 0 || mine.getRole() == 1) {
            if (parentid != null && !parentid.equals("") && !parentid.equals("undefined")) {
//				us =usersService.selectByUsername(parentid);
//				pName = us.getId();
                pName = parentid;
            }
            if (shopid != null && !shopid.equals("") && !shopid.equals("undefined")) {
//				us =usersService.selectByUsername(shopid);
//				sName = us.getId();
                sName = shopid;
            }
        }
        if (username != null && !username.equals("")) {
            mobile = username;
        }
        if (startTimeFrom != null && !startTimeFrom.equals("") && startTimeTo != null && !startTimeTo.equals("")) {
            sStartTimeFrom = startTimeFrom;
            sStartTimeTo = startTimeTo;
        }
        if (endTimeFrom != null && !endTimeFrom.equals("") && endTimeTo != null && !endTimeTo.equals("")) {
            sEndTimeFrom = endTimeFrom;
            sEndTimeTo = endTimeTo;
        }
        if (datastatus != null) {
            // 判断当前可用性
            switch (datastatus) {
                case 2:
                    status = false;
                    break;
                case 1:
                    status = true;
                    break;
                case 0:
                    break;
            }
        }
        page.setCurrentPageNo(1);
        if (mine.getRole() == 0) {
            page.setRecordTotal(subscribersService.countByAll(null, mobile, sUserName, pName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo));
            users = subscribersService.selectByAll(page, null, mobile, sUserName, pName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
        }

        if (mine.getRole() == 1) {
            page.setRecordTotal(subscribersService.countByParentid(adminUserId, null, mobile, sUserName, pName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo));
            users = subscribersService.selectByParentid(page, adminUserId, null, mobile, sUserName, pName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
        }

        if (mine.getRole() == 2) {
            page.setRecordTotal(subscribersService.countByShopidNotAll(adminUserId, null, mobile, sUserName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo));
            users = subscribersService.selectByShopidNotAll(page, adminUserId, null, mobile, sUserName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
        }
        if (choose == 2) {
            switch (mine.getRole()) {
                //  1. 判断是否是admin用户
                case 0:
                    page.setCurrentPageNo(1);
                    page.setRecordTotal(subscribersService.countByAll(null, null, null, null, null, null, null, null, null, null));
                    users = subscribersService.selectByAll(page, null, null, null, null, null, null, null, null, null, null);
                    break;
                //  2. 判断是否是OEM用户
                case 1:
                    page.setCurrentPageNo(1);
                    page.setRecordTotal(subscribersService.countByParentid(adminUserId, null, null, null, null, null, null, null, null, null, null));
                    users = subscribersService.selectByParentid(page, adminUserId, null, null, null, null, null, null, null, null, null, null);
                    break;
                //  2. 判断是否是商家用户
                case 2:
                    page.setCurrentPageNo(1);
                    page.setRecordTotal(subscribersService.countByShopidNotAll(adminUserId, null, null, null, null, null, null, null, null, null));
                    users = subscribersService.selectByShopidNotAll(page, adminUserId, null, null, null, null, null, null, null, null, null);
                    break;
            }
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出用户信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//		int totlenum = subscribersService.countByExample(examplePage);// 页面总数
        int totlenum = page.getRecordTotal();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万用户数据");
            if (users == null) {
                users = new ArrayList<Subscribers>();
            } else {
                users.clear();
            }
//			examplePage.start = start;
//			examplePage.rows = 10000;// 设置首次分页
            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);

//			users = subscribersService.selectByExamplePage(examplePage);
            if (mine.getRole() == 0) {
                users = subscribersService.selectByAll(page, null, mobile, sUserName, pName, sName, status,
                        sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
            }
            if (mine.getRole() == 1) {
                users = subscribersService.selectByParentid(page, adminUserId, null, mobile, sUserName, pName, sName, status,
                        sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
            }
            if (mine.getRole() == 2) {
                users = subscribersService.selectByShopidNotAll(page, adminUserId, null, mobile, sUserName, sName, status,
                        sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
            }

            //TODO 设置用户余额
            for (Subscribers u : users) {
                Users parent = usersService.selectByPrimaryKey(u.getParentid());
//				String result = VosHelper
//						.GetVosAccount("{accounts:['" + parent.getAgentprefix() + u.getMobile() + "']}");
//				if (result != null) {
//					VosModelList v1 = GsonUtil.getObject(result, VosModelList.class);
//					System.out.println(v1.retCode);
//					if (v1.retCode == 0) {
//						String w = "";
//						w = v1.infoCustomers[0].getMoney().toString();
//						u.setAliaccount(w);
//					} else {
//						u.setAliaccount("暂无记录");
//					}
//				}
            }

            System.out.println(">>>完成查询第" + (start + 1) + "万条用户数据的查找，共" + users.size() + "用户数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条用户数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("手机号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("注册时间");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("过期时间");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("账号余额");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("购物余额");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("状态");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("姓名");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("密码");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("所属OEM");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("所属商家");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("机构");
            for (int i = 0; i < users.size(); i++) {
                System.out.println(i);
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(users.get(i).getMobile());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(format.format(users.get(i).getCreatetime()));
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(users.get(i).getEndtime()));
                HSSFCell titlecell3 = row.createCell(3);
                Users parent = usersService.selectByPrimaryKey(users.get(i).getParentid());
//				String result = VosHelper.GetVosAccount("{accounts:['" + parent.getAgentprefix() + users.get(i).getMobile() + "']}");
//				VosModelList v1 = GsonUtil.getObject(result, VosModelList.class);
//				titlecell3.setCellValue(v1.infoCustomers[0].getMoney().toString());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(users.get(i).getShopBalance());
                HSSFCell titlecell5 = row.createCell(5);
                if (users.get(i).getDatastatus() == true) {
                    titlecell5.setCellValue("可用");
                }
                if (users.get(i).getDatastatus() == false) {
                    titlecell5.setCellValue("冻结");
                }
                HSSFCell titlecell6 = row.createCell(6);
                if (users.get(i).getUsername() == null) {
                    titlecell6.setCellValue("用户未上传姓名");
                } else {
                    titlecell6.setCellValue(users.get(i).getUsername());
                }
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(users.get(i).getPassword());
                HSSFCell titlecell8 = row.createCell(8);
                Users pUser = usersService.selectByPrimaryKey(users.get(i).getParentid());
                if (pUser != null && pUser.getUsername() != null) {
                    titlecell8.setCellValue(pUser.getUsername());
                } else {
                    titlecell8.setCellValue("未查询到商家");
                }
                HSSFCell titlecell9 = row.createCell(9);
                Users sUser = usersService.selectByPrimaryKey(users.get(i).getShopid());
                if (sUser != null && sUser.getUsername() != null) {
                    titlecell9.setCellValue(sUser.getUsername());
                } else {
                    titlecell9.setCellValue("未查询到商家");
                }
                HSSFCell titlecell10 = row.createCell(10);
                if (sUser != null) {
                    Users sTopUser = usersService.selectByPrimaryKey(sUser.getShopid());
                    if (sTopUser != null && sTopUser.getUsername() != null) {
                        titlecell10.setCellValue(sTopUser.getUsername());
                    } else {
                        titlecell10.setCellValue("未查询到上级商家");
                    }
                }
            }
            System.out.println((start + 1) + "万条用户信息完成导出<<<");
        }
        // 剩下部分导出
//		if (examplePage.start * 10000 != totlenum) {
//			examplePage.start += 1;
//			examplePage.rows = examplePage.pagesize;// 设置首次分页
//			if (users == null) {
//				users = new ArrayList<Subscribers>();
//			} else {
//				users.clear();
//			}
//			users = subscribersService.selectByExamplePage(examplePage);
//			// 新建工作表
//			HSSFSheet sheet = workbook.createSheet("第" + (examplePage.start + 1) + "万条用户信息");
//			for (int i = 0; i < users.size(); i++) {
//				HSSFRow row = sheet.createRow(i);
//				// 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
//				HSSFCell cell = row.createCell(0);
//				// 设置单元格的值,即C1的值(第一行,第三列)
//				cell.setCellValue(users.get(i).getId());
//			}
//		}
        // 输出到磁盘中
        // FileOutputStream fos = new FileOutputStream(
        // new File("C:\\Users\\Administrator\\Desktop\\批次号" + batch +
        // "充值卡.xls"));
        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/用户信息查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        file = new File(request.getServerName() + ":" + request.getServerPort() + "/file/Excel/用户信息查询结果.xls");

        out.print(file);
    }

    /**
     * 用户导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/subscribersExportNew", method = RequestMethod.POST)
    public void subscribersExportNew(HttpServletRequest request, HttpServletResponse response,
                                     String adminUserId, // 操作用户的id
                                     String username, // 商家名称
                                     String parentid, // 所属代理商
                                     String shopid, // 所属商家
                                     Integer datastatus,
                                     String startTimeFrom, String startTimeTo,
                                     String endTimeFrom, String endTimeTo) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<Subscribers> users = null;
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        if (parentid.equals("undefined")) {
            parentid = null;
        }
        if (shopid.equals("undefined")) {
            shopid = null;
        }

        Integer choose = 1;
        // 如果choose为1 条件查询 choose为2 查询所有
        if (startTimeFrom != null && !startTimeFrom.equals("") && startTimeTo != null && !startTimeTo.equals("")
                || endTimeFrom != null && !endTimeFrom.equals("") && endTimeTo != null && !endTimeTo.equals("")
                || username != null && !username.equals("") || parentid != null && !parentid.equals("")
                || shopid != null && !shopid.equals("")) {
            choose = 1;
        } else {
            choose = 2;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String pName = null;
        String sName = null;
        String mobile = null;
        String sUserName = null;
        Boolean status = null;
        String sStartTimeFrom = null;
        String sStartTimeTo = null;
        String sEndTimeFrom = null;
        String sEndTimeTo = null;
        Page page = new Page();

        if (mine.getRole() == 0 || mine.getRole() == 1) {
            if (parentid != null && !parentid.equals("") && !parentid.equals("undefined")) {
//				us =usersService.selectByUsername(parentid);
//				pName = us.getId();
                pName = parentid;
            }
            if (shopid != null && !shopid.equals("") && !shopid.equals("undefined")) {
//				us =usersService.selectByUsername(shopid);
//				sName = us.getId();
                sName = shopid;
            }
        }
        if (username != null && !username.equals("")) {
            mobile = username;
        }
        if (startTimeFrom != null && !startTimeFrom.equals("") && startTimeTo != null && !startTimeTo.equals("")) {
            sStartTimeFrom = startTimeFrom;
            sStartTimeTo = startTimeTo;
        }
        if (endTimeFrom != null && !endTimeFrom.equals("") && endTimeTo != null && !endTimeTo.equals("")) {
            sEndTimeFrom = endTimeFrom;
            sEndTimeTo = endTimeTo;
        }
        if (datastatus != null) {
            // 判断当前可用性
            switch (datastatus) {
                case 2:
                    status = false;
                    break;
                case 1:
                    status = true;
                    break;
                case 0:
                    break;
            }
        }
        page.setCurrentPageNo(1);
        page.setPageSize(100000);
        page.setRecordTotal(100000);
        if (mine.getRole() == 0) {
            page.setRecordTotal(subscribersService.countByAll(null, mobile, sUserName, pName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo));
            users = subscribersService.selectByAllForExport(sUserName, pName, sName, sStartTimeFrom, sStartTimeTo, 0, 10000);
        }

        if (mine.getRole() == 1) {
            users = subscribersService.selectByParentid(page, adminUserId, null, mobile, sUserName, pName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
        }

        if (mine.getRole() == 2) {
            users = subscribersService.selectByShopidNotAll(page, adminUserId, null, mobile, sUserName, sName, status,
                    sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo);
        }
        if (choose == 2) {
            switch (mine.getRole()) {
                //  1. 判断是否是admin用户
                case 0:
                    page.setRecordTotal(subscribersService.countByAll(null, mobile, sUserName, pName, sName, status,
                            sStartTimeFrom, sStartTimeTo, sEndTimeFrom, sEndTimeTo));
                    users = subscribersService.selectByAllForExport(null, null, null, null, null, 0, 10000);
                    break;
                //  2. 判断是否是OEM用户
                case 1:
                    users = subscribersService.selectByParentid(page, adminUserId, null, null, null, null, null, null, null, null, null, null);
                    break;
                //  2. 判断是否是商家用户
                case 2:
                    users = subscribersService.selectByShopidNotAll(page, adminUserId, null, null, null, null, null, null, null, null, null);
                    break;
            }
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出用户信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = page.getRecordTotal();
        if (totlenum > 200000) {
            System.out.println("数据量超过二十万！请重新选择！");
            out.print("数据量超过二十万！请重新选择！");
            return;
        }
        int out_times = totlenum / 10000;
        int in_times = 10000;
        if (totlenum < in_times) {
            in_times = totlenum;
        }
        for (int start = 0; start <= out_times; start++) {
            System.out.println("查询第" + (start + 1) + "万用户数据");

            System.out.println(">>>完成查询第" + (start + 1) + "万条用户数据的查找，共" + users.size() + "用户数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条用户数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("手机号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("注册时间");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("过期时间");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("账号余额");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("购物余额");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("状态");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("姓名");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("密码");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("所属OEM");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("所属商家");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("机构");
            for (int i = 0; i < in_times; i++) {
                System.out.println(i);
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(users.get(i).getMobile());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(format.format(users.get(i).getCreatetime()));
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(users.get(i).getEndtime()));
                HSSFCell titlecell3 = row.createCell(3);
                Users parent = usersService.selectByPrimaryKey(users.get(i).getParentid());
                String result = VosHelper.GetVosAccount("{accounts:['" + parent.getAgentprefix() + users.get(i).getMobile() + "']}");
                VosModelList v1 = GsonUtil.getObject(result, VosModelList.class);
                titlecell3.setCellValue(v1.infoCustomers[0].getMoney().toString());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(users.get(i).getShopBalance());
                HSSFCell titlecell5 = row.createCell(5);
                if (users.get(i).getDatastatus() == true) {
                    titlecell5.setCellValue("可用");
                }
                if (users.get(i).getDatastatus() == false) {
                    titlecell5.setCellValue("冻结");
                }
                HSSFCell titlecell6 = row.createCell(6);
                if (users.get(i).getUsername() == null) {
                    titlecell6.setCellValue("");
                } else {
                    titlecell6.setCellValue(users.get(i).getUsername());
                }
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(users.get(i).getPassword());
                HSSFCell titlecell8 = row.createCell(8);
                Users pUser = usersService.selectByPrimaryKey(users.get(i).getParentid());
                if (pUser != null && pUser.getUsername() != null) {
                    titlecell8.setCellValue(pUser.getUsername());
                } else {
                    titlecell8.setCellValue("未查询到商家");
                }
                HSSFCell titlecell9 = row.createCell(9);
                Users sUser = usersService.selectByPrimaryKey(users.get(i).getShopid());
                if (sUser != null && sUser.getUsername() != null) {
                    titlecell9.setCellValue(sUser.getUsername());
                } else {
                    titlecell9.setCellValue("未查询到商家");
                }
                HSSFCell titlecell10 = row.createCell(10);
                if (sUser != null) {
                    Users sTopUser = usersService.selectByPrimaryKey(sUser.getShopid());
                    if (sTopUser != null && sTopUser.getUsername() != null) {
                        titlecell10.setCellValue(sTopUser.getUsername());
                    } else {
                        titlecell10.setCellValue("未查询到上级商家");
                    }
                }
            }

            users = subscribersService.selectByAllForExport(sUserName, pName, sName, startTimeFrom, startTimeTo, (start + 1) * 10000, 10000);

            System.out.println((start + 1) + "万条用户信息完成导出<<<");
        }
        // 输出到磁盘中
        // FileOutputStream fos = new FileOutputStream(
        // new File("C:\\Users\\Administrator\\Desktop\\批次号" + batch +
        // "充值卡.xls"));
        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/" + format.format(Produce.getNowDate()) + "用户信息查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + out_times);

        file = new File(CommUrls.Url + "file/Excel/" + format.format(Produce.getNowDate()) + "用户信息查询结果.xls");

        out.print(file);
    }

    @RequestMapping(value = "/pqExportSub")
    public void subExport(HttpServletRequest request, HttpServletResponse response, String userid) throws IOException {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<String> users = subscribersService.selectMobiles(userid);
        Users parent = usersService.selectByPrimaryKey(userid);
        Integer total = users.size();
        Page page = new Page();
        page.setRecordTotal(total);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出用户信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//        int totlenum = page.getRecordTotal();
//        if (totlenum > 200000) {
//            System.out.println("数据量超过二十万！请重新选择！");
//            return;
//        }
//        int out_times = totlenum / 10000;
//        int in_times = 10000;
//        if (totlenum < in_times) {
//            in_times = totlenum;
//        }
        // 新建工作表
        HSSFSheet sheet = workbook.createSheet("用户数据");
        // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
        HSSFRow title = sheet.createRow(0);
        HSSFCell tc0 = title.createCell(0);
        tc0.setCellValue("手机号");
        HSSFCell tc1 = title.createCell(1);
        tc1.setCellValue("账号余额");
        for (int i = 0; i < users.size(); i++) {
            System.out.println(i);
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell titlecell0 = row.createCell(0);
            titlecell0.setCellValue(users.get(i));
            HSSFCell titlecell1 = row.createCell(1);
            String result = VosHelper.GetVosAccount("{accounts:['" + parent.getAgentprefix() + users.get(i) + "']}");
            VosModelList v1 = GsonUtil.getObject(result, VosModelList.class);
            titlecell1.setCellValue(v1.infoCustomers[0].getMoney().toString());
        }

//            users = subscribersService.selectByAllForExport(sUserName, pName, sName, startTimeFrom, startTimeTo, (start + 1) * 10000, 10000);
        // 输出到磁盘中
        // FileOutputStream fos = new FileOutputStream(
        // new File("C:\\Users\\Administrator\\Desktop\\批次号" + batch +
        // "充值卡.xls"));
        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/" + format.format(Produce.getNowDate()) + "用户信息查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + users.size() + "times:" + endtime);

        file = new File(CommUrls.Url + "file/Excel/" + format.format(Produce.getNowDate()) + "用户信息查询结果.xls");

        out.print(file);
    }


    /**
     * 代理商导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/oemExport", method = RequestMethod.POST)
    public void oemExport(HttpServletRequest request, HttpServletResponse response,
                          String adminUserId, // 操作用户的id
                          String username) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<Users> users = null;
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        Integer choose = 2;
        if (mine.getRole() < 2) {
            if (username != null && !username.equals("")) {
                choose = 1;
            }
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String pName = null;
        Boolean status = null;
        Page page = new Page();

        if (username != null && !username.equals("")) {
            pName = username;
        }

        page.setCurrentPageNo(1);
        if (mine.getRole() == 0) {
            page.setRecordTotal(usersService.countOEMByAll(pName, status));
            users = usersService.selectOEMByAll(page, pName, status);
        }

        if (mine.getRole() == 1) {
            page.setRecordTotal(usersService.countOEMByParentID(adminUserId, pName, status));
            users = usersService.selectOEMByParentID(page, adminUserId, pName, status);
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出OEM信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//		int totlenum = subscribersService.countByExample(examplePage);// 页面总数
        int totlenum = page.getRecordTotal();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万商家数据");
            if (users == null) {
                users = new ArrayList<Users>();
            } else {
                users.clear();
            }
//			examplePage.start = start;
//			examplePage.rows = 10000;// 设置首次分页
            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);

//			users = subscribersService.selectByExamplePage(examplePage);
            if (mine.getRole() == 0) {
                users = usersService.selectOEMByAll(page, pName, status);
            }
            if (mine.getRole() == 1) {
                users = usersService.selectOEMByParentID(page, adminUserId, pName, status);
            }

            System.out.println(">>>完成查询第" + (start + 1) + "万条OEM数据的查找，共" + users.size() + "OEM数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条OEM数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("代理商");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("密码");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("状态");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("系统消耗");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("透支");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("话费");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("购物");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("短信");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("费率");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("中继");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("APPKEY");
            HSSFCell tc11 = title.createCell(11);
            tc11.setCellValue("淘宝授权");
            for (int i = 0; i < users.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(users.get(i).getUsername());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(users.get(i).getPassword());
                HSSFCell titlecell2 = row.createCell(2);
                if (users.get(i).getDatastatus() == true) {
                    titlecell2.setCellValue("可用");
                } else {
                    titlecell2.setCellValue("冻结");
                }
                HSSFCell titlecell3 = row.createCell(3);
//				try {
//					String result1 = VosHelper.GetVosAccount("{accounts:['" + users.get(i).getUsername() + "j']}");
//					VosModelList v2 = GsonUtil.getObject(result1, VosModelList.class);
//					if (v2 != null && v2.infoCustomers.length != 0) {
//						InfoCustomers info2 = v2.infoCustomers[0];
//						titlecell3.setCellValue(new BigDecimal(new DecimalFormat("0.00").format(info2.getMoney())).toString());
//					} else {
//						titlecell3.setCellValue(0.00);
//					}
//				} catch (Exception e) {
//					titlecell3.setCellValue(0.00);
//				}
                BigDecimal xh = callbackinfoesService.sumOmoneyParentID(users.get(i).getId());
                if (xh == null) {
                    titlecell3.setCellValue(0.00);
                } else {
                    titlecell3.setCellValue(new DecimalFormat("#0.00").format(xh));
                }
                HSSFCell titlecell4 = row.createCell(4);
                try {
                    String result1 = VosHelper.GetVosAccount("{accounts:['" + users.get(i).getUsername() + "j']}");
                    VosModelList v2 = GsonUtil.getObject(result1, VosModelList.class);
                    if (v2 != null && v2.infoCustomers.length != 0) {
                        InfoCustomers info2 = v2.infoCustomers[0];
                        titlecell4.setCellValue(info2.getLimitMoney().intValue());
                        // TODO
//						u.setFreeroute(info1.getFeeRateGroup());// 费率，数据库中已经保存
                    } else {
                        titlecell4.setCellValue(0.00);
                    }
                } catch (Exception e) {
                    titlecell4.setCellValue(0.00);
                }
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(users.get(i).getBlance().toString());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(users.get(i).getShopblance());
                HSSFCell titlecell7 = row.createCell(7);
                List<Smsconfigs> sms = smsconfigsService.selectByAgentIdAll(users.get(i).getId());
                if (sms != null && sms.size() > 0) {
                    titlecell7.setCellValue(sms.get(0).getCount());
                } else {
                    titlecell7.setCellValue(0);
                }
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(users.get(i).getFreeroute());
                HSSFCell titlecell9 = row.createCell(9);
                switch (users.get(i).getCalltype()) {
                    case 1:
                        titlecell9.setCellValue("JH线路");
                        break;
                    case 2:
                        titlecell9.setCellValue("MY线路");
                        break;
                    case 3:
                        titlecell9.setCellValue("ZP线路");
                        break;
                    case 4:
                        titlecell9.setCellValue("ST线路");
                        break;
                }
                HSSFCell titlecell10 = row.createCell(10);
                titlecell10.setCellValue(users.get(i).getId());
                HSSFCell titlecell11 = row.createCell(11);
                titlecell11.setCellValue(users.get(i).getSqappkey());
            }

            System.out.println((start + 1) + "万条oem信息完成导出<<<");
        }
        // 剩下部分导出
//		if (examplePage.start * 10000 != totlenum) {
//			examplePage.start += 1;
//			examplePage.rows = examplePage.pagesize;// 设置首次分页
//			if (users == null) {
//				users = new ArrayList<Subscribers>();
//			} else {
//				users.clear();
//			}
//			users = subscribersService.selectByExamplePage(examplePage);
//			// 新建工作表
//			HSSFSheet sheet = workbook.createSheet("第" + (examplePage.start + 1) + "万条用户信息");
//			for (int i = 0; i < users.size(); i++) {
//				HSSFRow row = sheet.createRow(i);
//				// 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
//				HSSFCell cell = row.createCell(0);
//				// 设置单元格的值,即C1的值(第一行,第三列)
//				cell.setCellValue(users.get(i).getId());
//			}
//		}
        // 输出到磁盘中
        // FileOutputStream fos = new FileOutputStream(
        // new File("C:\\Users\\Administrator\\Desktop\\批次号" + batch +
        // "充值卡.xls"));
        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/OEM信息查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        file = new File(request.getServerName() + ":" + request.getServerPort() + "/file/Excel/OEM信息查询结果.xls");

        out.print(file);
    }

    /**
     * 商家导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/shopExport", method = RequestMethod.POST)
    public void shopExport(HttpServletRequest request, HttpServletResponse response,
                           String adminUserId, // 操作用户的id
                           String username, // 商家名称
                           String parentid, // 所属代理商
                           String shopid, // 所属商家
                           Integer datastatus) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<Users> users = null;
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        if (parentid.equals("undefined")) {
            parentid = null;
        }
        if (shopid.equals("undefined")) {
            shopid = null;
        }

        Integer choose = 2;
        if (mine.getRole() < 2) {
            if (username != null && !username.equals("") || shopid != null && !shopid.equals("") ||
                    parentid != null && !parentid.equals("") || datastatus != 0) {
                choose = 1;
            }
        }
        if (mine.getRole() == 2) {
            if (username != null && !username.equals("") || datastatus != 0) {
                choose = 1;
            }
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String sUserName = null;
        String pId = null;
        String sId = null;
        Boolean sDatastatus = null;
        Page page = new Page();

        if (username != null && !username.equals("")) {
            sUserName = username;
        }
        if (parentid != null && !parentid.equals("") && !parentid.equals("undefined")) {
            pId = parentid;
        }
        if (shopid != null && !shopid.equals("") && !shopid.equals("undefined")) {
            sId = shopid;
        }
        if (datastatus != null) {
            // 判断当前可用性
            switch (datastatus) {
                case 2:
                    sDatastatus = false;
                    break;
                case 1:
                    sDatastatus = true;
                    break;
                case 0:
                    break;
            }
        }
        page.setCurrentPageNo(1);
        if (mine.getRole() == 0) {
            page.setRecordTotal(usersService.countByAll(sUserName, pId, sId, sDatastatus));
            users = usersService.selectByAll(page, sUserName, pId, sId, sDatastatus);
        }

        if (mine.getRole() == 1) {
            page.setRecordTotal(usersService.countByParentID(adminUserId, sUserName, pId, sId, sDatastatus));
            users = usersService.selectByParentID(page, adminUserId, sUserName, pId, sId, sDatastatus);
        }

        if (mine.getRole() == 2) {
            page.setRecordTotal(usersService.countByShopIDNotAll(adminUserId, sUserName, pId, sId, sDatastatus));
            users = usersService.selectByShopIDNotAll(page, adminUserId, sUserName, pId, sId, sDatastatus);
        }

        if (choose == 2) {
            switch (mine.getRole()) {
                case 0:
                    page.setRecordTotal(usersService.countByAll(null, null, null, null));
                    users = usersService.selectByAll(page, null, null, null, null);
                    break;
                case 1:
                    // 查询OEMID下的用户
                    // 查询OEM下的商户以及OEM
                    // 查询OEM id
                    page.setRecordTotal(usersService.countByParentID(adminUserId, null, null, null, null));
                    users = usersService.selectByParentID(page, adminUserId, null, null, null, null);
                    break;
                case 2:
                    //当前商家的上级oem
                    page.setRecordTotal(usersService.countByShopIDNotAll(adminUserId, null, null, null, null));
                    users = usersService.selectByShopIDNotAll(page, adminUserId, null, null, null, null);
                    break;
            }
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出商家信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//		int totlenum = subscribersService.countByExample(examplePage);// 页面总数
        int totlenum = page.getRecordTotal();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万商家数据");
            if (users == null) {
                users = new ArrayList<Users>();
            } else {
                users.clear();
            }
//			examplePage.start = start;
//			examplePage.rows = 10000;// 设置首次分页
            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);

//			users = subscribersService.selectByExamplePage(examplePage);
            if (mine.getRole() == 0) {
                users = usersService.selectByAll(page, sUserName, pId, sId, sDatastatus);
            }
            if (mine.getRole() == 1) {
                users = usersService.selectByParentID(page, adminUserId, sUserName, pId, sId, sDatastatus);
            }
            if (mine.getRole() == 2) {
                users = usersService.selectByShopIDNotAll(page, adminUserId, sUserName, pId, sId, sDatastatus);
            }

            System.out.println(">>>完成查询第" + (start + 1) + "万条商家数据的查找，共" + users.size() + "商家数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条商家数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("账号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("注册时间");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("所属代理商");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("机构");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("上级推荐人");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("今日直推");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("今日充值");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("累计充值");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("累计用户");
            for (int i = 0; i < users.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(users.get(i).getUsername());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(format.format(users.get(i).getCreatetime()));
                HSSFCell titlecell2 = row.createCell(2);
                Users pUser = usersService.selectByPrimaryKey(users.get(i).getParentid());
                if (pUser != null && pUser.getUsername() != null) {
                    titlecell2.setCellValue(pUser.getUsername());
                } else {
                    titlecell2.setCellValue("未查询到代理商");
                }
                HSSFCell titlecell3 = row.createCell(3);
                Users sUser = usersService.selectByPrimaryKey(users.get(i).getShopid());
                if (sUser != null && sUser.getUsername() != null) {
                    titlecell3.setCellValue(sUser.getUsername());
                } else {
                    titlecell3.setCellValue("未查询到商家");
                }
                HSSFCell titlecell4 = row.createCell(4);
                if (sUser != null) {
                    Users sTopUser = usersService.selectByPrimaryKey(sUser.getShopid());
                    if (sTopUser != null && sTopUser.getUsername() != null) {
                        titlecell4.setCellValue(sTopUser.getUsername());
                    } else {
                        titlecell4.setCellValue("未查询到上级商家");
                    }
                }
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(subscribersService.selectByCountShop(users.get(i).getId(), Parameters.getDayBegin(),
                        Parameters.getDayEnd()));
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(payinfoesService.countDay(users.get(i).getId(), Parameters.getDayBegin(),
                        Parameters.getDayEnd()));
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(payinfoesService.countDay(users.get(i).getId(), null, null));
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(subscribersService.selectByCountShop(users.get(i).getId(), null, null));
            }
            System.out.println((start + 1) + "万条商家信息完成导出<<<");
        }
        // 剩下部分导出
//		if (examplePage.start * 10000 != totlenum) {
//			examplePage.start += 1;
//			examplePage.rows = examplePage.pagesize;// 设置首次分页
//			if (users == null) {
//				users = new ArrayList<Subscribers>();
//			} else {
//				users.clear();
//			}
//			users = subscribersService.selectByExamplePage(examplePage);
//			// 新建工作表
//			HSSFSheet sheet = workbook.createSheet("第" + (examplePage.start + 1) + "万条用户信息");
//			for (int i = 0; i < users.size(); i++) {
//				HSSFRow row = sheet.createRow(i);
//				// 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
//				HSSFCell cell = row.createCell(0);
//				// 设置单元格的值,即C1的值(第一行,第三列)
//				cell.setCellValue(users.get(i).getId());
//			}
//		}
        // 输出到磁盘中
        // FileOutputStream fos = new FileOutputStream(
        // new File("C:\\Users\\Administrator\\Desktop\\批次号" + batch +
        // "充值卡.xls"));
        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/商家信息查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        file = new File(request.getServerName() + ":" + request.getServerPort() + "/file/Excel/商家信息查询结果.xls");

        out.print(file);
    }

    /**
     * 商家导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/callBackExport", method = RequestMethod.POST)
    public void callBackExport(HttpServletRequest request, HttpServletResponse response,
                               String adminUserId, String date) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<Callbackinfoes> callbackinfoes = null;
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String pId = null;
        String fCaller = null;
        String fCalle164 = null;
        String dateFrom = null;
        String dateTo = null;
        Page page = new Page();

        String[] sourceStrArray = date.trim().split("-");
        for (int i = 0; i < sourceStrArray.length; i++) {
            System.out.println(sourceStrArray[i]);
            if (dateFrom == null) {
                dateFrom = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.strToDateLong(sourceStrArray[i]));
            } else {
                dateTo = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.strToDateLong(sourceStrArray[i]));
            }
        }
        System.out.println(dateFrom + "/" + dateTo);

        page.setCurrentPageNo(1);
        if (mine.getRole() == 0) {
            page.setRecordTotal(callbackinfoesService.countByParentID(adminUserId, fCaller, fCalle164, dateFrom, dateTo));
            callbackinfoes = callbackinfoesService.selectByParentID(page, adminUserId, fCaller, fCalle164, dateFrom, dateTo);
        }

        if (mine.getRole() == 1) {
            page.setRecordTotal(callbackinfoesService.countByParentID(adminUserId, fCaller, fCalle164, dateFrom, dateTo));
            callbackinfoes = callbackinfoesService.selectByParentID(page, adminUserId, fCaller, fCalle164, dateFrom, dateTo);
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出通话记录信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//		int totlenum = subscribersService.countByExample(examplePage);// 页面总数
        int totlenum = page.getRecordTotal();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万通话记录");
            if (callbackinfoes == null) {
                callbackinfoes = new ArrayList<Callbackinfoes>();
            } else {
                callbackinfoes.clear();
            }
//			examplePage.start = start;
//			examplePage.rows = 10000;// 设置首次分页
            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);

//			users = subscribersService.selectByExamplePage(examplePage);
            if (mine.getRole() == 0) {
                callbackinfoes = callbackinfoesService.selectByParentID(page, adminUserId, fCaller, fCalle164, dateFrom, dateTo);
            }
            if (mine.getRole() == 1) {
                callbackinfoes = callbackinfoesService.selectByParentID(page, adminUserId, fCaller, fCalle164, dateFrom, dateTo);
            }

            System.out.println(">>>完成查询第" + (start + 1) + "万条通话记录的查找，共" + callbackinfoes.size() + "通话记录<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条通话记录");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("主叫");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("被叫");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("拨打时间");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("通话时长");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("通话费用");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("线路费用");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("状态");
            for (int i = 0; i < callbackinfoes.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(callbackinfoes.get(i).getCaller());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(callbackinfoes.get(i).getCalle164());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(callbackinfoes.get(i).getCalltime()));
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(callbackinfoes.get(i).getTimecount());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(callbackinfoes.get(i).getMoney().toString());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(callbackinfoes.get(i).getOmoney().toString());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(callbackinfoes.get(i).getStatus());
            }
            System.out.println((start + 1) + "万条通话记录信息完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/通话记录查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        file = new File(request.getServerName() + ":" + request.getServerPort() + "/file/Excel/通话记录查询结果.xls");

        out.print(file);
    }
    /**
     * 自建商城订单导出新版
     */
    @RequestMapping("/outSelfShopOrderNew")
    public void
    outSelfShopOrderNew(HttpServletRequest request, HttpServletResponse response,String goodstext,
                              String ordernum, String mobile, Byte status, Byte deliverystatus, String supplier, String createtime,String selfShopName) throws Exception {
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Users users = usersService.selectByPrimaryKey(adminUserId);

        //即将导出的所有的商品
        List<SelfShopOrder> selfShopOrder = null;
        Integer choose = 2;
        if (goodstext != null && !goodstext.equals("") || mobile != null && !mobile.equals("")
                || ordernum != null && !ordernum.equals("")
                || supplier != null && !supplier.equals("")
                || createtime != null && !createtime.equals("")
                || status != 0 || deliverystatus != 2) {
            choose = 1;
        }

        String fGoodsText = null;
        String fMobile = null;
        String fOrderNum = null;
        Byte fStatus = null;
        Byte fDeliveryStatus = null;
        String fSupplier = null;
        String fCreatetime = null;

        if (goodstext != null && !goodstext.equals("")) {
            fGoodsText = goodstext;
        }
        if (mobile != null && !mobile.equals("")) {
            fMobile = mobile;
        }
        if (ordernum != null && !ordernum.equals("")) {
            fOrderNum = ordernum;
        }
        if (status != 0) {
            fStatus = status;
        }
        if (deliverystatus != 2) {
            fDeliveryStatus = deliverystatus;
        }
        if (supplier != null && !supplier.equals("")) {
            fSupplier = supplier;
        }
        if (createtime != null && !createtime.equals("")) {
            fCreatetime = createtime;
        }

        //累计收益
        BigDecimal allCash = new BigDecimal(0);
        //可提现收益
        BigDecimal canCash = new BigDecimal(0);
        //提现记录
        List<SelfShopWithdrawals> selfShopWithdrawals = null;

        // TODO 2020-11-17 兴农有道显示全部
        String selfShopUserId = null;
        if (!adminUserId.equals("d34f8cb561bd48a992556f4faed45e2d")){
            selfShopUserId = adminUserId;
        }
        //店铺信息
        List<SelfShop> selfShopList = null;
        String shopid=null;
        //根据selfShopName查询shopId
        if(selfShopName!=null&&selfShopName!=""){
            SelfShop s1=selfShopService.selectByName(selfShopName);
            selfShopOrder=selfShopOrderService.selectBySelfShopIDALL(s1.getId(),fMobile,fOrderNum,fSupplier,fStatus,fCreatetime);
        }else{
            if (users.getRole() == 0 || users.getRole() == 1) {
                selfShopList = selfShopService.selectBySelfShop(adminUserId, adminUserId);
                selfShopOrder = selfShopOrderService.selectByAllNoPage(adminUserId, null, fMobile, fOrderNum, fGoodsText,
                        fStatus, fDeliveryStatus, fSupplier, fCreatetime,users.getRole());
                allCash = selfShopOrderService.sumAllCash(adminUserId, adminUserId);
                selfShopWithdrawals = selfShopWithdrawalsService.selectWithdrawals(adminUserId, adminUserId);

                try {
                    if (selfShopWithdrawals != null && selfShopWithdrawals.size() > 0) {
                        canCash = selfShopOrderService.sumCanCash(adminUserId, adminUserId,
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopWithdrawals.get(0).getCreatetime()),
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                    } else {
                        canCash = selfShopOrderService.sumCanCash(adminUserId, adminUserId,
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopList.get(0).getCreatetime()),
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                    }
                } catch (Exception e) {
                    canCash = new BigDecimal(0);
                }
            }
            if (users.getRole() == 2) {
                selfShopList = selfShopService.selectBySelfShop(users.getParentid(), adminUserId);

                selfShopOrder = selfShopOrderService.selectByAllNoPage(users.getParentid(), selfShopUserId, fMobile, fOrderNum, fGoodsText,
                        fStatus, fDeliveryStatus, fSupplier, fCreatetime,users.getRole());
                allCash = selfShopOrderService.sumAllCash(users.getParentid(), adminUserId);

                selfShopWithdrawals = selfShopWithdrawalsService.selectWithdrawals(users.getParentid(), adminUserId);
                try {
                    if (selfShopWithdrawals != null && selfShopWithdrawals.size() > 0) {
                        canCash = selfShopOrderService.sumCanCash(users.getParentid(), adminUserId,
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopWithdrawals.get(0).getCreatetime()),
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                    } else {
                        canCash = selfShopOrderService.sumCanCash(users.getParentid(), adminUserId,
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopList.get(0).getCreatetime()),
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                    }
                } catch (Exception e) {
                    canCash = new BigDecimal(0);
                }
            }

            if (choose == 2) {
                if (users.getRole() == 0 || users.getRole() == 1) {
                    if(shopid!=null){
                        selfShopList = selfShopService.selectBySelfShop(adminUserId, shopid);

                        selfShopOrder = selfShopOrderService.selectByAllNoPage(adminUserId, shopid, fMobile, fOrderNum, fGoodsText,
                                fStatus, fDeliveryStatus, fSupplier, fCreatetime,users.getRole());
                        allCash = selfShopOrderService.sumAllCash(adminUserId, shopid);
                        selfShopWithdrawals = selfShopWithdrawalsService.selectWithdrawals(adminUserId, shopid);
                    }else{
                        selfShopList = selfShopService.selectBySelfShop(adminUserId, adminUserId);
                        selfShopOrder = selfShopOrderService.selectByAllNoPage( adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                                fStatus, fDeliveryStatus, fSupplier, fCreatetime,users.getRole());
                        allCash = selfShopOrderService.sumAllCash(adminUserId, adminUserId);
                        selfShopWithdrawals = selfShopWithdrawalsService.selectWithdrawals(adminUserId, adminUserId);
                    }
                    try {
                        if (selfShopWithdrawals != null && selfShopWithdrawals.size() > 0) {
                            canCash = selfShopOrderService.sumCanCash(adminUserId, adminUserId,
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopWithdrawals.get(0).getCreatetime()),
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                        } else {
                            canCash = selfShopOrderService.sumCanCash(adminUserId, adminUserId,
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopList.get(0).getCreatetime()),
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                        }
                    } catch (Exception e) {
                        canCash = new BigDecimal(0);
                    }
                }
                if (users.getRole() == 2) {
                    selfShopList = selfShopService.selectBySelfShop(users.getParentid(), adminUserId);
                    selfShopOrder = selfShopOrderService.selectByAllNoPage(users.getParentid(), adminUserId, null, null, null, null, null, null, null,users.getRole());
                    allCash = selfShopOrderService.sumAllCash(users.getParentid(), adminUserId);

                    selfShopWithdrawals = selfShopWithdrawalsService.selectWithdrawals(users.getParentid(), adminUserId);
                    try {
                        if (selfShopWithdrawals != null && selfShopWithdrawals.size() > 0) {
                            canCash = selfShopOrderService.sumCanCash(users.getParentid(), adminUserId,
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopWithdrawals.get(0).getCreatetime()),
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                        } else {
                            canCash = selfShopOrderService.sumCanCash(users.getParentid(), adminUserId,
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(selfShopList.get(0).getCreatetime()),
                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Produce.getNowDate()));
                        }
                    } catch (Exception e) {
                        canCash = new BigDecimal(0);
                    }
                }
            }
        }

//        List<Map<String,Object>> selfShopOrderMap= Lists.newArrayList();
//        for (SelfShopOrder shopOrder : selfShopOrder) {
//            Map<String,Object> ma= MapUtils.object2Map(shopOrder);
//            SelfShopOrder selfShopOrder1=selfShopOrderService.selectByPrimaryKey(shopOrder.getId());
//            Subscribers payUser=subscribersService.selectByPrimaryKey(selfShopOrder1.getUserid());
//            Users users1=usersService.selectByPrimaryKey(payUser.getShopid());
//            ma.put("shopname",users1.getUsername());
//            selfShopOrderMap.add(ma);
//        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出自建商城订单信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = selfShopOrder.size();
        if (totlenum > 10000) {
            System.out.println("数据量超过一万！请重新选择！");
            out.print("数据量超过一万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万自建商城订单数据");
//            if (selfShopOrder == null) {
//                selfShopOrder = new ArrayList<SelfShopOrder>();
//            } else {
//                selfShopOrder.clear();
//            }


            System.out.println(">>>完成查询第" + (start + 1) + "万条自建商城订单数据的查找，共" + selfShopOrder.size() + "自建商城订单数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条自建商城订单数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("账号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("订单号");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("创建时间");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("商品名");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("收件人");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("收件地址");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("收件人号码");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("支付金额");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("抵扣金额");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("话费余额");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("支付方式");
            HSSFCell tc11 = title.createCell(11);
            tc11.setCellValue("支付状态");
            HSSFCell tc12 = title.createCell(12);
            tc12.setCellValue("支付时间");
            HSSFCell tc13 = title.createCell(13);
            tc13.setCellValue("发货状态");
            HSSFCell tc14 = title.createCell(14);
            tc14.setCellValue("发货时间");
            HSSFCell tc15 = title.createCell(15);
            tc15.setCellValue("快递单号");
            HSSFCell tc116 = title.createCell(16);
            tc116.setCellValue("数量");
            HSSFCell tc117 = title.createCell(17);
            tc117.setCellValue("颜色");
            HSSFCell tc118 = title.createCell(18);
            tc118.setCellValue("尺寸");
            HSSFCell tc119 = title.createCell(19);
            tc119.setCellValue("用户所属商家");
            HSSFCell tc120 = title.createCell(20);
            tc120.setCellValue("商品所属商家");
            for (int i = 0; i < selfShopOrder.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(selfShopOrder.get(i).getSubscribers().getMobile());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(selfShopOrder.get(i).getOrdernum());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(selfShopOrder.get(i).getCreatetime()));
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(selfShopOrder.get(i).getSelfShopGoods().getGoodstext());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(selfShopOrder.get(i).getAddressee());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(selfShopOrder.get(i).getAddress());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(selfShopOrder.get(i).getPhonenumber());
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(selfShopOrder.get(i).getCash().toString());
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(selfShopOrder.get(i).getDerate().toString());
                HSSFCell titlecell9 = row.createCell(9);
                titlecell9.setCellValue(selfShopOrder.get(i).getBalance().toString());
                HSSFCell titlecell10 = row.createCell(10);
                String payMethod = null;
                if (selfShopOrder.get(i).getPaymethod()!=null){
                    switch (selfShopOrder.get(i).getPaymethod()) {
                        case 0:
                            payMethod = "未知";
                            break;
                        case 1:
                            payMethod = "支付宝";
                            break;
                        case 2:
                            payMethod = "微信";
                            break;
                        case 3:
                            payMethod = "话费全抵";
                            break;
                        default:
                            break;
                    }
                }
                titlecell10.setCellValue(payMethod);
                HSSFCell titlecell11 = row.createCell(11);
                String payStatus = null;
                switch (selfShopOrder.get(i).getStatus()) {
                    case 1:
                        payStatus = "未支付";
                        break;
                    case 2:
                        payStatus = "已支付";
                        break;
                    default:
                        break;
                }
                titlecell11.setCellValue(payStatus);
                HSSFCell titlecell12 = row.createCell(12);
                if (selfShopOrder.get(i).getPaytime() != null) {
                    titlecell12.setCellValue(format.format(selfShopOrder.get(i).getPaytime()));
                } else {
                    titlecell12.setCellValue("");
                }
                HSSFCell titlecell13 = row.createCell(13);
                String deliveryStatus = null;
                switch (selfShopOrder.get(i).getDeliverystatus()) {
                    case 1:
                        deliveryStatus = "未支付";
                        break;
                    case 2:
                        deliveryStatus = "已支付";
                        break;
                    default:
                        break;
                }
                titlecell13.setCellValue(deliveryStatus);
                HSSFCell titlecell14 = row.createCell(14);
                if (selfShopOrder.get(i).getDeliverytime() != null) {
                    titlecell14.setCellValue(format.format(selfShopOrder.get(i).getDeliverytime()));
                } else {
                    titlecell14.setCellValue("");
                }
                HSSFCell titlecell15 = row.createCell(15);
                titlecell15.setCellValue(selfShopOrder.get(i).getExpressnumber() == null ? "" : selfShopOrder.get(i).getExpressnumber());
                HSSFCell titlecell16 = row.createCell(16);
                titlecell16.setCellValue(selfShopOrder.get(i).getNumber());
                HSSFCell titlecell17 = row.createCell(17);
                titlecell17.setCellValue(selfShopOrder.get(i).getColor() == null ? "" : selfShopOrder.get(i).getColor());
                HSSFCell titlecell18 = row.createCell(18);
                titlecell18.setCellValue(selfShopOrder.get(i).getSize() == null ? "" : selfShopOrder.get(i).getSize());
                try{
                    SelfShopOrder selfShopOrder1=selfShopOrderService.selectByPrimaryKey(selfShopOrder.get(i).getId());
                    Subscribers payUser=subscribersService.selectByPrimaryKey(selfShopOrder1.getUserid());
                    Users users1=usersService.selectByPrimaryKey(payUser.getShopid());
                    HSSFCell titlecell19 = row.createCell(19);
                    titlecell19.setCellValue(users1.getUsername());
                }catch (Exception e){
                    HSSFCell titlecell19 = row.createCell(19);
                    titlecell19.setCellValue("未知商家");
                }
                try{
                    SelfShopOrder selfShopOrder2=selfShopOrderService.selectByPrimaryKey(selfShopOrder.get(i).getId());
                    SelfShopGoods selfShopGoods=selfShopGoodsService.selectByPrimaryKey(selfShopOrder2.getGoodsid());
                    Users users2=usersService.selectByPrimaryKey(selfShopGoods.getShopid());
                    HSSFCell titlecell20 = row.createCell(20);
                    titlecell20.setCellValue(users2.getUsername());
                }catch (Exception e){
                    HSSFCell titlecell20 = row.createCell(20);
                    titlecell20.setCellValue("未知商家");
                }
            }
            System.out.println((start + 1) + "万条商家信息完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/新版自建商城信息导出.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/新版自建商城信息导出.xls";
        file = new File(fileName);

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }
    /**
     * 自建商城订单导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/selfShopOrderExport", method = RequestMethod.POST)
    public void selfShopOrderExport(HttpServletRequest request, HttpServletResponse response,
                                    String adminUserId, // 操作用户的id
                                    String goodstext, // 商品名
                                    String ordernum, // 订单号
                                    String mobile, // 手机号
                                    Byte status, // 支付状态
                                    Byte deliverystatus) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<SelfShopOrder> selfShopOrder = null;
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        Integer choose = 2;
        if (goodstext != null && !goodstext.equals("") || ordernum != null && !ordernum.equals("") ||
                mobile != null && !mobile.equals("") || status != 0 || deliverystatus != 2) {
            choose = 1;
        }

        String fGoodsText = null;
        String fMobile = null;
        String fOrderNum = null;
        Byte fStatus = null;
        Byte fDeliveryStatus = null;
        Page page = new Page();

        if (goodstext != null && !goodstext.equals("")) {
            fGoodsText = goodstext;
        }
        if (mobile != null && !mobile.equals("")) {
            fMobile = mobile;
        }
        if (ordernum != null && !ordernum.equals("")) {
            fOrderNum = ordernum;
        }
        if (status != 0) {
            fStatus = status;
        }
        if (deliverystatus != 2) {
            fDeliveryStatus = deliverystatus;
        }

        page.setCurrentPageNo(1);
        if (mine.getRole() == 0) {
            page.setRecordTotal(selfShopOrderService.countByAll(adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                    fStatus, fDeliveryStatus, null, null, mine.getRole()));
            selfShopOrder = selfShopOrderService.selectByAll(page, adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                    fStatus, fDeliveryStatus, null, null, mine.getRole());
        }

        if (mine.getRole() == 1) {
            page.setRecordTotal(selfShopOrderService.countByAll(adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                    fStatus, fDeliveryStatus, null, null, mine.getRole()));
            selfShopOrder = selfShopOrderService.selectByAll(page, adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                    fStatus, fDeliveryStatus, null, null, mine.getRole());
        }

        if (mine.getRole() == 2) {
            page.setRecordTotal(selfShopOrderService.countByAll(mine.getParentid(), adminUserId, fMobile, fOrderNum, fGoodsText,
                    fStatus, fDeliveryStatus, null, null, mine.getRole()));
            selfShopOrder = selfShopOrderService.selectByAll(page, mine.getParentid(), adminUserId, fMobile, fOrderNum, fGoodsText,
                    fStatus, fDeliveryStatus, null, null, mine.getRole());
        }

        if (choose == 2) {
            switch (mine.getRole()) {
                case 0:
                    page.setRecordTotal(selfShopOrderService.countByAll(adminUserId, adminUserId, null, null, null, null, null, null, null, mine.getRole()));
                    selfShopOrder = selfShopOrderService.selectByAll(page, adminUserId, adminUserId, null, null, null, null, null, null, null, mine.getRole());
                    break;
                case 1:
                    page.setRecordTotal(selfShopOrderService.countByAll(adminUserId, adminUserId, null, null, null, null, null, null, null, mine.getRole()));
                    selfShopOrder = selfShopOrderService.selectByAll(page, adminUserId, adminUserId, null, null, null, null, null, null, null, mine.getRole());
                    break;
                case 2:
                    page.setRecordTotal(selfShopOrderService.countByAll(mine.getParentid(), adminUserId, null, null, null, null, null, null, null, mine.getRole()));
                    selfShopOrder = selfShopOrderService.selectByAll(page, mine.getParentid(), adminUserId, null, null, null, null, null, null, null, mine.getRole());
                    break;
            }
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出自建商城订单信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = page.getRecordTotal();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万自建商城订单数据");
            if (selfShopOrder == null) {
                selfShopOrder = new ArrayList<SelfShopOrder>();
            } else {
                selfShopOrder.clear();
            }

            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);

            if (mine.getRole() == 0) {
                selfShopOrder = selfShopOrderService.selectByAll(page, adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                        fStatus, fDeliveryStatus, null, null, mine.getRole());
            }
            if (mine.getRole() == 1) {
                selfShopOrder = selfShopOrderService.selectByAll(page, adminUserId, adminUserId, fMobile, fOrderNum, fGoodsText,
                        fStatus, fDeliveryStatus, null, null, mine.getRole());
            }
            if (mine.getRole() == 2) {
                selfShopOrder = selfShopOrderService.selectByAll(page, mine.getParentid(), adminUserId, fMobile, fOrderNum, fGoodsText,
                        fStatus, fDeliveryStatus, null, null, mine.getRole());
            }
            for (SelfShopOrder shopOrder : selfShopOrder) {
                try{
                    SelfShopOrder selfShopOrder1=selfShopOrderService.selectByPrimaryKey(shopOrder.getId());
                    Subscribers payUser=subscribersService.selectByPrimaryKey(selfShopOrder1.getUserid());
                    Users users1=usersService.selectByPrimaryKey(payUser.getShopid());
                    shopOrder.setMessage(users1.getUsername());
                }catch (Exception e){
                    shopOrder.setMessage("未知");
                }

            }
            System.out.println(">>>完成查询第" + (start + 1) + "万条自建商城订单数据的查找，共" + selfShopOrder.size() + "自建商城订单数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条自建商城订单数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("账号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("订单号");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("创建时间");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("商品名");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("收件人");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("收件地址");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("收件人号码");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("支付金额");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("抵扣金额");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("话费余额");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("支付方式");
            HSSFCell tc11 = title.createCell(11);
            tc11.setCellValue("支付状态");
            HSSFCell tc12 = title.createCell(12);
            tc12.setCellValue("支付时间");
            HSSFCell tc13 = title.createCell(13);
            tc13.setCellValue("发货状态");
            HSSFCell tc14 = title.createCell(14);
            tc14.setCellValue("发货时间");
            HSSFCell tc15 = title.createCell(15);
            tc15.setCellValue("快递单号");
            HSSFCell tc116 = title.createCell(16);
            tc116.setCellValue("数量");
            HSSFCell tc117 = title.createCell(17);
            tc117.setCellValue("颜色");
            HSSFCell tc118 = title.createCell(18);
            tc118.setCellValue("尺寸");
            HSSFCell tc119 = title.createCell(19);
            tc119.setCellValue("所属商家");
            for (int i = 0; i < selfShopOrder.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                titlecell0.setCellValue(selfShopOrder.get(i).getSubscribers().getMobile());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(selfShopOrder.get(i).getOrdernum());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(selfShopOrder.get(i).getCreatetime()));
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(selfShopOrder.get(i).getSelfShopGoods().getGoodstext());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(selfShopOrder.get(i).getAddressee());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(selfShopOrder.get(i).getAddress());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(selfShopOrder.get(i).getPhonenumber());
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(selfShopOrder.get(i).getCash().toString());
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(selfShopOrder.get(i).getDerate().toString());
                HSSFCell titlecell9 = row.createCell(9);
                titlecell9.setCellValue(selfShopOrder.get(i).getBalance().toString());
                HSSFCell titlecell10 = row.createCell(10);
                String payMethod = null;
                if (selfShopOrder.get(i).getPaymethod()!=null){
                    switch (selfShopOrder.get(i).getPaymethod()) {
                        case 0:
                            payMethod = "未知";
                            break;
                        case 1:
                            payMethod = "支付宝";
                            break;
                        case 2:
                            payMethod = "微信";
                            break;
                        case 3:
                            payMethod = "话费全抵";
                            break;
                        default:
                            break;
                    }
                }
                titlecell10.setCellValue(payMethod);
                HSSFCell titlecell11 = row.createCell(11);
                String payStatus = null;
                switch (selfShopOrder.get(i).getStatus()) {
                    case 1:
                        payStatus = "未支付";
                        break;
                    case 2:
                        payStatus = "已支付";
                        break;
                    default:
                        break;
                }
                titlecell11.setCellValue(payStatus);
                HSSFCell titlecell12 = row.createCell(12);
                if (selfShopOrder.get(i).getPaytime() != null) {
                    titlecell12.setCellValue(format.format(selfShopOrder.get(i).getPaytime()));
                } else {
                    titlecell12.setCellValue("");
                }
                HSSFCell titlecell13 = row.createCell(13);
                String deliveryStatus = null;
                switch (selfShopOrder.get(i).getDeliverystatus()) {
                    case 1:
                        deliveryStatus = "未支付";
                        break;
                    case 2:
                        deliveryStatus = "已支付";
                        break;
                    default:
                        break;
                }
                titlecell13.setCellValue(deliveryStatus);
                HSSFCell titlecell14 = row.createCell(14);
                if (selfShopOrder.get(i).getDeliverytime() != null) {
                    titlecell14.setCellValue(format.format(selfShopOrder.get(i).getDeliverytime()));
                } else {
                    titlecell14.setCellValue("");
                }
                HSSFCell titlecell15 = row.createCell(15);
                titlecell15.setCellValue(selfShopOrder.get(i).getExpressnumber() == null ? "" : selfShopOrder.get(i).getExpressnumber());
                HSSFCell titlecell16 = row.createCell(16);
                titlecell16.setCellValue(selfShopOrder.get(i).getNumber());
                HSSFCell titlecell17 = row.createCell(17);
                titlecell17.setCellValue(selfShopOrder.get(i).getColor() == null ? "" : selfShopOrder.get(i).getColor());
                HSSFCell titlecell18 = row.createCell(18);
                titlecell18.setCellValue(selfShopOrder.get(i).getSize() == null ? "" : selfShopOrder.get(i).getSize());
                HSSFCell titlecell19 = row.createCell(19);
                titlecell19.setCellValue(selfShopOrder.get(i).getMessage() == null ? "" : selfShopOrder.get(i).getMessage());

            }
            System.out.println((start + 1) + "万条商家信息完成导出<<<");
        }
        // 剩下部分导出
//		if (examplePage.start * 10000 != totlenum) {
//			examplePage.start += 1;
//			examplePage.rows = examplePage.pagesize;// 设置首次分页
//			if (users == null) {
//				users = new ArrayList<Subscribers>();
//			} else {
//				users.clear();
//			}
//			users = subscribersService.selectByExamplePage(examplePage);
//			// 新建工作表
//			HSSFSheet sheet = workbook.createSheet("第" + (examplePage.start + 1) + "万条用户信息");
//			for (int i = 0; i < users.size(); i++) {
//				HSSFRow row = sheet.createRow(i);
//				// 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
//				HSSFCell cell = row.createCell(0);
//				// 设置单元格的值,即C1的值(第一行,第三列)
//				cell.setCellValue(users.get(i).getId());
//			}
//		}
        // 输出到磁盘中
        // FileOutputStream fos = new FileOutputStream(
        // new File("C:\\Users\\Administrator\\Desktop\\批次号" + batch +
        // "充值卡.xls"));
        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/自建商城订单查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/自建商城订单查询结果.xls";
        file = new File(fileName);

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    /**
     * 自建商城订单导出
     */
    @SuppressWarnings({"resource"})
    @RequestMapping(value = "/selfShopOrderExportXT", method = RequestMethod.POST)
    public void selfShopOrderExport(HttpServletRequest request, HttpServletResponse response,
                                    @RequestParam("arrOrder") JSONArray arrOrder) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        List<SelfShopOrder> selfShopOrder = new ArrayList<>();

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        for (int i = 0; i < arrOrder.size(); i++) {
            SelfShopOrder order = selfShopOrderService.selectByPrimaryKey(arrOrder.get(i).toString());
            selfShopOrder.add(order);
        }

        /**
         * TODO 开始导用户信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出自建商城订单信息<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = selfShopOrder.size();
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }
        int times = totlenum / 10000;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万自建商城订单数据");

            System.out.println(">>>完成查询第" + (start + 1) + "万条自建商城订单数据的查找，共" + selfShopOrder.size() + "自建商城订单数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条自建商城订单数据");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("账号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("订单号");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("创建时间");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("商品名");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("收件人");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("收件地址");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("收件人号码");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("支付金额");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("抵扣金额");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("话费余额");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("支付方式");
            HSSFCell tc11 = title.createCell(11);
            tc11.setCellValue("支付状态");
            HSSFCell tc12 = title.createCell(12);
            tc12.setCellValue("支付时间");
            HSSFCell tc13 = title.createCell(13);
            tc13.setCellValue("发货状态");
            HSSFCell tc14 = title.createCell(14);
            tc14.setCellValue("发货时间");
            HSSFCell tc15 = title.createCell(15);
            tc15.setCellValue("快递单号");
            HSSFCell tc116 = title.createCell(16);
            tc116.setCellValue("数量");
            HSSFCell tc117 = title.createCell(17);
            tc117.setCellValue("颜色");
            HSSFCell tc118 = title.createCell(18);
            tc118.setCellValue("尺寸");
            for (int i = 0; i < selfShopOrder.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell0 = row.createCell(0);
                Subscribers sub = subscribersService.selectByPrimaryKey(selfShopOrder.get(i).getUserid());
                titlecell0.setCellValue(sub.getMobile() == null ? "" : sub.getMobile());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(selfShopOrder.get(i).getOrdernum());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(selfShopOrder.get(i).getCreatetime()));
                HSSFCell titlecell3 = row.createCell(3);
                SelfShopGoods goods = selfShopGoodsService.selectById(selfShopOrder.get(i).getGoodsid());
                titlecell3.setCellValue(goods.getGoodstext() == null ? "" : goods.getGoodstext());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(selfShopOrder.get(i).getAddressee());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(selfShopOrder.get(i).getAddress());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(selfShopOrder.get(i).getPhonenumber());
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(selfShopOrder.get(i).getCash().toString());
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(selfShopOrder.get(i).getDerate().toString());
                HSSFCell titlecell9 = row.createCell(9);
                titlecell9.setCellValue(selfShopOrder.get(i).getBalance().toString());
                HSSFCell titlecell10 = row.createCell(10);
                String payMethod = null;
                switch (selfShopOrder.get(i).getPaymethod()) {
                    case 0:
                        payMethod = "未知";
                        break;
                    case 1:
                        payMethod = "支付宝";
                        break;
                    case 2:
                        payMethod = "微信";
                        break;
                    case 3:
                        payMethod = "话费全抵";
                        break;
                    default:
                        break;
                }
                titlecell10.setCellValue(payMethod);
                HSSFCell titlecell11 = row.createCell(11);
                String payStatus = null;
                switch (selfShopOrder.get(i).getStatus()) {
                    case 1:
                        payStatus = "未支付";
                        break;
                    case 2:
                        payStatus = "已支付";
                        break;
                    default:
                        break;
                }
                titlecell11.setCellValue(payStatus);
                HSSFCell titlecell12 = row.createCell(12);
                if (selfShopOrder.get(i).getPaytime() != null) {
                    titlecell12.setCellValue(format.format(selfShopOrder.get(i).getPaytime()));
                } else {
                    titlecell12.setCellValue("");
                }
                HSSFCell titlecell13 = row.createCell(13);
                String deliveryStatus = null;
                switch (selfShopOrder.get(i).getDeliverystatus()) {
                    case 1:
                        deliveryStatus = "未支付";
                        break;
                    case 2:
                        deliveryStatus = "已支付";
                        break;
                    default:
                        break;
                }
                titlecell13.setCellValue(deliveryStatus);
                HSSFCell titlecell14 = row.createCell(14);
                if (selfShopOrder.get(i).getDeliverytime() != null) {
                    titlecell14.setCellValue(format.format(selfShopOrder.get(i).getDeliverytime()));
                } else {
                    titlecell14.setCellValue("");
                }
                HSSFCell titlecell15 = row.createCell(15);
                titlecell15.setCellValue(selfShopOrder.get(i).getExpressnumber() == null ? "" : selfShopOrder.get(i).getExpressnumber());
                HSSFCell titlecell16 = row.createCell(16);
                titlecell16.setCellValue(selfShopOrder.get(i).getNumber());
                HSSFCell titlecell17 = row.createCell(17);
                titlecell17.setCellValue(selfShopOrder.get(i).getColor() == null ? "" : selfShopOrder.get(i).getColor());
                HSSFCell titlecell18 = row.createCell(18);
                titlecell18.setCellValue(selfShopOrder.get(i).getSize() == null ? "" : selfShopOrder.get(i).getSize());
            }
            System.out.println((start + 1) + "万条商家信息完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/自建商城订单查询结果.xls");


        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出用户信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);

        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/自建商城订单查询结果.xls";
        file = new File(fileName);

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/payinfoExport", method = RequestMethod.POST)
    public void export(HttpServletRequest request, HttpServletResponse response, Integer money, // 面值
                       String adminUserId, // 操作用户的id
                       String date) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        PayinfoesExamplePage examplePage = new PayinfoesExamplePage();
        examplePage.setOrderByClause("PayTime");
        List<Payinfoes> payinfoes = null;
        PayinfoesExample.Criteria criteria = examplePage.createCriteria();
        Users mine = usersService.selectByPrimaryKey(adminUserId);

        Integer choose = 1;
        // 设置时间
        String[] sourceStrArray = date.trim().split("-");
        Date dateFrom = null;
        Date dateTo = null;
        for (int i = 0; i < sourceStrArray.length; i++) {
            System.out.println(sourceStrArray[i]);
            if (dateFrom == null) {
                dateFrom = DateUtil.strToDateLong(sourceStrArray[i]);
            } else {
                dateTo = DateUtil.strToDateLong(sourceStrArray[i]);
            }
        }
        if (dateFrom != null) {
            request.setAttribute("date", date);
            if (dateTo != null) {
                criteria = criteria.andPaytimeBetween(dateFrom, dateTo);
            } else {
                criteria = criteria.andPaytimeBetween(dateFrom, Produce.getNowDate());
            }
            examplePage.getOredCriteria().add(criteria);
        }

        /**
         * TODO 查询一级oem和一级商家
         */
        if (mine.getRole() == 1) {
            List<String> list = usersService.selectByParentId(adminUserId);
            for (String oemId : list) {
                // OEM下的商家
                examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId));

            }
            examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId));
        }

        if (choose == 2) {
            switch (mine.getRole()) {
                case 0:
                    break;
                case 1:
                    // 查询OEMID下的用户
                    // 查询OEM下的商户以及OEM
                    List<String> l = usersService.selectByParentId(adminUserId);
                    for (String oemId : l) {
                        // OEM下的商家
                        examplePage.or(examplePage.createCriteria().andParentidEqualTo(oemId));
                    }
                    examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId));
                    break;

                case 2:
                    List<String> lists = usersService.selectByParentId(adminUserId);
                    System.out.println("Users的数量：" + lists.size());
                    for (String shopId : lists) {
                        // 商家下的商家
                        examplePage.or(examplePage.createCriteria().andParentidEqualTo(shopId));
                    }
                    examplePage.getOredCriteria().add(criteria.andParentidEqualTo(adminUserId));
                    break;
            }
        }

        /**
         * TODO 开始导出卡信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出充值记录<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = payinfoesService.countByExample(examplePage);// 页面总数
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        int times = totlenum / 10000;
        int end = 0;
        String aliStatus = null;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万条充值记录数据");
            if (payinfoes == null) {
                payinfoes = new ArrayList<Payinfoes>();
            } else {
                payinfoes.clear();
            }
            examplePage.start = start;
            examplePage.rows = 10000;// 设置首次分页
            payinfoes = payinfoesService.selectByExamplePage(examplePage);

            System.out.println(">>>完成查询第" + (start + 1) + "万条充值记录数据的查找，共" + payinfoes.size() + "条充值记录数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条充值记录");
            end = start + 1;
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("到账账户");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("充值方式");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("到账金额");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("到账状态");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("支付金额");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("支付状态");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("充值时间");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("所属商家");
            for (int i = 0; i < payinfoes.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(payinfoes.get(i).getMobile());
                HSSFCell titlecell1 = row.createCell(1);
                switch (payinfoes.get(i).getType()) {
                    case 1:
                        if (payinfoes.get(0).getCardtype() == 1) {
                            titlecell1.setCellValue("话费卡");
                        } else if (payinfoes.get(0).getCardtype() == 2) {
                            titlecell1.setCellValue("购物卡");
                        } else if (payinfoes.get(0).getCardtype() == 3) {
                            titlecell1.setCellValue("等值卡");
                        }
                        break;
                    case 2:
                        titlecell1.setCellValue("空中充值");
                        break;
                    case 3:
                        titlecell1.setCellValue("支付宝");
                        aliStatus = payinfoes.get(i).getAlistatus() == "2" ? "支付成功" : "支付失败";
                        break;
                    case 4:
                        titlecell1.setCellValue("微信");
                        aliStatus = payinfoes.get(i).getAlistatus() == "2" ? "支付成功" : "支付失败";
                        break;
                    case 5:
                        titlecell1.setCellValue("银联");
                        aliStatus = payinfoes.get(i).getAlistatus() == "2" ? "支付成功" : "支付失败";
                        break;
                }
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(payinfoes.get(i).getMoney());
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(payinfoes.get(0).getStatus() == true ? "充值到账" : "充值未到账");
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(payinfoes.get(0).getAlimoney());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(aliStatus);
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(format.format(payinfoes.get(i).getPaytime()));
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(usersService.selectByPrimaryKey(payinfoes.get(i).getParentid()).getUsername());
            }
            System.out.println((start + 1) + "万张卡完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/充值记录查询结果.xls");

        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出卡信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/充值记录查询结果.xls";
        file = new File(fileName);

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/gasOrderExport", method = RequestMethod.POST)
    public void gasOrderExport(HttpServletRequest request, HttpServletResponse response, String dateRange,
                               String createTime1, String createTime2) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();

        //截取时间
        if (dateRange != null && !dateRange.equals("")) {
            String[] str = dateRange.split(" - ");
            createTime1 = str[0];
            createTime2 = str[1];
        }

        // 设置分页 获取内容
        Page page = new Page(); // 实例化分页
        page.setCurrentPageNo(1);

        Integer gasOrderCount = gasOrderH5readService.countAllGasOrder(null, null, null, null, null, createTime1, createTime2);
        List<GasOrderH5read> gasOrders = gasOrderH5readService.selectAllGasOrder(null, null, null, null, null, createTime1, createTime2, page);

        /**
         * TODO 开始导出卡信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出充值记录<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = gasOrderCount;// 页面总数
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        int times = totlenum / 10000;
        int end = 0;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万条充值记录数据");
            if (gasOrders == null) {
                gasOrders = new ArrayList<GasOrderH5read>();
            } else {
                gasOrders.clear();
            }

            page.setCurrentPageNo(start);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);
            gasOrders = gasOrderH5readService.selectAllGasOrder(null, null, null, null, null, createTime1, createTime2, page);

            System.out.println(">>>完成查询第" + (start + 1) + "万条充值记录数据的查找，共" + gasOrders.size() + "条记录数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条记录");
            end = start + 1;
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("订单");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("用户手机号");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("所属商家");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("油站名称");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("升数");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("订单总金额");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("支付金额");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("订单状态");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("省");
            HSSFCell tc9 = title.createCell(9);
            tc9.setCellValue("市");
            HSSFCell tc10 = title.createCell(10);
            tc10.setCellValue("区");
            HSSFCell tc11 = title.createCell(11);
            tc11.setCellValue("是否扣除话费");
            HSSFCell tc12 = title.createCell(12);
            tc12.setCellValue("订单创建时间");
            for (int i = 0; i < gasOrders.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(gasOrders.get(i).getOrderid());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(gasOrders.get(i).getUsermobile());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(gasOrders.get(i).getParentName());
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(gasOrders.get(i).getGasname());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(gasOrders.get(i).getLitre());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(gasOrders.get(i).getAmountgun().toString());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(gasOrders.get(i).getAmountpay().toString());
                HSSFCell titlecell7 = row.createCell(7);
                titlecell7.setCellValue(gasOrders.get(i).getOrderstatusname());
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(gasOrders.get(i).getProvince());
                HSSFCell titlecell9 = row.createCell(9);
                titlecell9.setCellValue(gasOrders.get(i).getCity());
                HSSFCell titlecell10 = row.createCell(10);
                titlecell10.setCellValue(gasOrders.get(i).getCounty());
                HSSFCell titlecell11 = row.createCell(11);
                if (gasOrders.get(i).getIspaymoney() == 0) {
                    titlecell11.setCellValue("未支付");
                } else {
                    titlecell11.setCellValue("已支付");
                }
                HSSFCell titlecell12 = row.createCell(12);
                titlecell12.setCellValue(gasOrders.get(i).getOrderdt());
            }
            System.out.println((start + 1) + "万条数据完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/加油订单查询结果.xls");

        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出卡信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/加油订单查询结果.xls";
        file = new File(fileName);

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/logsExport", method = RequestMethod.POST)
    public void logsExport(HttpServletRequest request, HttpServletResponse response,
                           String timeFrom, String timeTo) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        PrintWriter out = response.getWriter();

        //截取时间
        String fTimeFrom = null;
        String fTimeTo = null;
        if (timeFrom != null && !timeFrom.equals("") && timeTo != null && !timeTo.equals("")) {
            fTimeFrom = timeFrom + " 00:00:00";
            fTimeTo = timeTo + " 23:59:59";
        } else {
            out.print("请填写时间段");
        }

        // 设置分页 获取内容
        Page page = new Page(); // 实例化分页

        List<Operationlogs> operationlogs = null;
        Users user = null;
        if (adminUsergrade == 0) {
            page.setCurrentPageNo(1);
            page.setRecordTotal(logsService.countByAll(null, null, fTimeFrom, fTimeTo));
            operationlogs = logsService.selectByAll(page, null, null, fTimeFrom, fTimeTo);
        }
        if (adminUsergrade == 1 || adminUsergrade == 2) {
            user = usersService.selectByPrimaryKey(adminUserId);
            page.setCurrentPageNo(1);
            page.setRecordTotal(logsService.countByName(user.getUsername(), null, fTimeFrom, fTimeTo));
            operationlogs = logsService.selectByName(page, user.getUsername(), null, fTimeFrom, fTimeTo);
        }

        /**
         * TODO 开始导出卡信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出充值记录<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = page.getRecordTotal();// 页面总数
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

        int times = totlenum / 10000;
        int end = 0;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万条充值记录数据");
            if (operationlogs == null) {
                operationlogs = new ArrayList<Operationlogs>();
            } else {
                operationlogs.clear();
            }

            page.setCurrentPageNo(start + 1);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);

            if (adminUsergrade == 0) {
                page.setRecordTotal(logsService.countByAll(null, null, fTimeFrom, fTimeTo));
                operationlogs = logsService.selectByAll(page, null, null, fTimeFrom, fTimeTo);
            }
            if (adminUsergrade == 1 || adminUsergrade == 2) {
                user = usersService.selectByPrimaryKey(adminUserId);
                page.setRecordTotal(logsService.countByName(user.getUsername(), null, fTimeFrom, fTimeTo));
                operationlogs = logsService.selectByName(page, user.getUsername(), null, fTimeFrom, fTimeTo);
            }

            System.out.println(">>>完成查询第" + (start + 1) + "万条充值记录数据的查找，共" + operationlogs.size() + "条记录数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条记录");
            end = start + 1;
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("登陆IP");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("登陆用户");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("操作日记");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("最近登陆时间");
            for (int i = 0; i < operationlogs.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(operationlogs.get(i).getIpinfo());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(operationlogs.get(i).getName());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(operationlogs.get(i).getContent());
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(format.format(operationlogs.get(i).getCreatetime()));
            }
            System.out.println((start + 1) + "万条数据完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/操作日志查询结果.xls");

        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出卡信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/操作日志查询结果.xls";
        file = new File(fileName);

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/trafficCardExport", method = RequestMethod.POST)
    public void trafficCardExport(HttpServletRequest request, HttpServletResponse response,
                                  String timeFrom, String timeTo) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        PrintWriter out = response.getWriter();
        if (adminUsergrade.equals(0)) {
            adminUserId = null;
        }
        //截取时间
        String fTimeFrom = null;
        String fTimeTo = null;
        if (timeFrom != null && !timeFrom.equals("") && timeTo != null && !timeTo.equals("")) {
            fTimeFrom = timeFrom;
            fTimeTo = timeTo;
        } else {
            out.print("请填写时间段");
        }

        // 设置分页 获取内容
        Page page = new Page(); // 实例化分页

        List<TrafficCardApplication> applications = null;
        Users user = null;
        if (adminUsergrade == 0) {
            page.setRecordTotal(trafficCardApplicationService.countByAll(null, null, null, fTimeFrom, fTimeTo));
            applications = trafficCardApplicationService.selectByAll(page, null, null, null, fTimeFrom, fTimeTo);
        }
        if (adminUsergrade == 1) {
            page.setCurrentPageNo(1);
            page.setRecordTotal(trafficCardApplicationService.countByAll(adminUserId, null, null, fTimeFrom, fTimeTo));
            applications = trafficCardApplicationService.selectByAll(page, adminUserId, null, null, fTimeFrom, fTimeTo);
        }
        if (adminUsergrade == 2) {
            page.setCurrentPageNo(1);
            page.setRecordTotal(trafficCardApplicationService.countByAll(null, adminUserId, null, fTimeFrom, fTimeTo));
            applications = trafficCardApplicationService.selectByAll(page, null, adminUserId, null, fTimeFrom, fTimeTo);
        }

        /**
         * TODO 开始导出卡信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出充值记录<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = page.getRecordTotal();// 页面总数
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

        int times = totlenum / 10000;
        int end = 0;
        for (int start = 0; start <= times; start++) {
//			System.out.println("查询第" + (start + 1) + "万条充值记录数据");
            if (applications == null) {
                applications = new ArrayList<TrafficCardApplication>();
            } else {
                applications.clear();
            }

            page.setCurrentPageNo(start + 1);
            page.setCurrentPage(start * 10000);
            page.setPageSize(10000);
            if (adminUsergrade == 0) {
                page.setRecordTotal(trafficCardApplicationService.countByAll(null, null, null, fTimeFrom, fTimeTo));
                applications = trafficCardApplicationService.selectByAll(page, null, null, null, fTimeFrom, fTimeTo);
            }
            if (adminUsergrade == 1) {
                page.setRecordTotal(trafficCardApplicationService.countByAll(adminUserId, null, null, fTimeFrom, fTimeTo));
                applications = trafficCardApplicationService.selectByAll(page, adminUserId, null, null, fTimeFrom, fTimeTo);
            }
            if (adminUsergrade == 2) {
                page.setRecordTotal(trafficCardApplicationService.countByAll(null, adminUserId, null, fTimeFrom, fTimeTo));
                applications = trafficCardApplicationService.selectByAll(page, null, adminUserId, null, fTimeFrom, fTimeTo);
            }

//			System.out.println(">>>完成查询第" + (start + 1) + "万条记录数据的查找，共" + applications.size() + "条记录数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条记录");
            end = start + 1;
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("订单号");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("代理商");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("商家");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("姓名");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("身份证");
            HSSFCell tc5 = title.createCell(5);
            tc5.setCellValue("手机号");
            HSSFCell tc6 = title.createCell(6);
            tc6.setCellValue("地址");
            HSSFCell tc7 = title.createCell(7);
            tc7.setCellValue("类型");
            HSSFCell tc8 = title.createCell(8);
            tc8.setCellValue("创建时间");
            for (int i = 0; i < applications.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(applications.get(i).getId());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(usersService.selectByPrimaryKey(applications.get(i).getParentid()).getUsername());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(usersService.selectByPrimaryKey(applications.get(i).getShopid()).getUsername());
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(applications.get(i).getName());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(applications.get(i).getIdcard());
                HSSFCell titlecell5 = row.createCell(5);
                titlecell5.setCellValue(applications.get(i).getPhone());
                HSSFCell titlecell6 = row.createCell(6);
                titlecell6.setCellValue(applications.get(i).getAddress());
                HSSFCell titlecell7 = row.createCell(7);
                switch (applications.get(i).getTraffictype()) {
                    case 1:
                        titlecell7.setCellValue("一个月");
                        break;
                    case 2:
                        titlecell7.setCellValue("二个月");
                        break;
                    case 3:
                        titlecell7.setCellValue("三个月");
                        break;
                }
                HSSFCell titlecell8 = row.createCell(8);
                titlecell8.setCellValue(format.format(applications.get(i).getCreatetime()));
            }
            System.out.println((start + 1) + "万条数据完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/大王卡查询结果.xls");

        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出卡信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/大王卡查询结果.xls";
        file = new File(fileName);

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/addressBookExport", method = RequestMethod.POST)
    public void addressBookExport(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject obj) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        PrintWriter out = response.getWriter();

        //返回数据
        Map<String, String> map = new HashMap<>();
        // 服务器域名协议
        String headPath = null;

        List<BookContext> info = new ArrayList<>();
        try {
            String addressbook = obj.get("addressbook").toString();
            Map<String, Object> infoMap = new HashedMap();
            infoMap.put("addressbookinfo", BookContext.class);
            AddressBookContext context = (AddressBookContext) JSONObject.toBean(JSONObject.fromObject(addressbook), AddressBookContext.class, infoMap);
            info = context.getAddressbookinfo();
        } catch (NullPointerException e) {
            headPath = "4";
            map.put("headPath", headPath);
            JSONObject json = JSONObject.fromObject(map);
            out.print(json);
            return;
        } catch (JSONException j) {
            headPath = "5";
            map.put("headPath", headPath);
            JSONObject json = JSONObject.fromObject(map);
            out.print(json);
            return;
        } catch (Exception e) {
            headPath = "6";
            map.put("headPath", headPath);
            JSONObject json = JSONObject.fromObject(map);
            out.print(json);
            return;
        }


        /**
         * TODO 开始导出信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出记录<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = info.size();// 页面总数
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

        for (int start = 0; start <= 1; start++) {
            System.out.println(">>>完成查询第" + (start + 1) + "万条记录数据的查找，共" + info.size() + "条记录数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条记录");
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("拼音");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("手机号");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("通讯录备注名");
            HSSFCell tc3 = title.createCell(3);
            tc3.setCellValue("contact_id");
            HSSFCell tc4 = title.createCell(4);
            tc4.setCellValue("account");
            for (int i = 0; i < info.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(info.get(i).getPinyin());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(info.get(i).getPhone());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(info.get(i).getName());
                HSSFCell titlecell3 = row.createCell(3);
                titlecell3.setCellValue(info.get(i).getContact_id());
                HSSFCell titlecell4 = row.createCell(4);
                titlecell4.setCellValue(info.get(i).getAccount());
            }
            System.out.println((start + 1) + "万条数据完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        file = new File(filePath+"Excel/通讯录查询结果.xls");

        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/通讯录查询结果.xls";
        file = new File(fileName);

        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/cardOperateExport", method = RequestMethod.POST)
    public void cardOperateExport(HttpServletRequest request, HttpServletResponse response, String name,
                                  String timeFrom, String timeTo, Integer type) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        OperationlogsExamplePage examplePage = new OperationlogsExamplePage();
        examplePage.setOrderByClause("CreateTime");
        List<Operationlogs> operationlogs = null;
        OperationlogsExample.Criteria criteria = examplePage.createCriteria();
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");

        Users user = null;
        if (name != null && !name.equals("")) {
            criteria = criteria.andNameLike("%" + name + "%");
            examplePage.getOredCriteria().add(criteria);
        }
        if (timeFrom != null && !timeFrom.equals("") && timeTo != null && !timeTo.equals("")) {
            request.setAttribute("timeFrom", timeFrom);
            request.setAttribute("timeTo", timeTo);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            criteria = criteria.andCreatetimeBetween(sdf.parse(timeFrom), sdf.parse(timeTo));
            examplePage.getOredCriteria().add(criteria);
        }

        //admin 显示所有
        if (adminUsergrade == 0) {
            examplePage.getOredCriteria().add(criteria.andTypeEqualTo(type));
            operationlogs = logsService.selectByExamplePage(examplePage);
        }
        //OEM 显示其划拨日志
        if (adminUsergrade == 1) {
            user = usersService.selectByPrimaryKey(adminUserId);
            List<String> list = usersService.selectUserNameByParentId(adminUserId);
            examplePage.getOredCriteria().add(criteria.andTypeEqualTo(type).andNameEqualTo(user.getUsername()));
            operationlogs = logsService.selectByExamplePage(examplePage);
        }
        //商家显示划拨日志
        if (adminUsergrade == 2) {
            user = usersService.selectByPrimaryKey(adminUserId);
            List<String> list = usersService.selectUserNameByShopId(adminUserId);
            examplePage.getOredCriteria().add(criteria.andTypeEqualTo(type).andNameEqualTo(user.getUsername()));
            operationlogs = logsService.selectByExamplePage(examplePage);
        }

        /**
         * TODO 开始导出卡信息
         */
        long startTime = System.currentTimeMillis();
        System.out.println(">>>>>>开始导出充值记录<<<<<<<");
        /**
         * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls 07版本和07版本以后的做法excel文件的后缀名为.xlsx
         */
        // 创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        int totlenum = logsService.countByExample(examplePage);// 页面总数
        if (totlenum > 100000) {
            System.out.println("数据量超过十万！请重新选择！");
            out.print("数据量超过十万！请重新选择！");
            return;
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        int times = totlenum / 10000;
        int end = 0;
        String aliStatus = null;
        for (int start = 0; start <= times; start++) {
            System.out.println("查询第" + (start + 1) + "万条记录数据");
            if (operationlogs == null) {
                operationlogs = new ArrayList<Operationlogs>();
            } else {
                operationlogs.clear();
            }
            examplePage.start = start;
            examplePage.rows = 10000;// 设置首次分页
            operationlogs = logsService.selectByExamplePage(examplePage);

            System.out.println(">>>完成查询第" + (start + 1) + "万条记录数据的查找，共" + operationlogs.size() + "条记录数据<<<");
            // 新建工作表
            HSSFSheet sheet = workbook.createSheet("第" + (start + 1) + "万条充值记录");
            end = start + 1;
            // 创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
            HSSFRow title = sheet.createRow(0);
            HSSFCell tc0 = title.createCell(0);
            tc0.setCellValue("操作人");
            HSSFCell tc1 = title.createCell(1);
            tc1.setCellValue("操作内容");
            HSSFCell tc2 = title.createCell(2);
            tc2.setCellValue("操作时间");
            for (int i = 0; i < operationlogs.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell titlecell = row.createCell(0);
                titlecell.setCellValue(operationlogs.get(i).getName());
                HSSFCell titlecell1 = row.createCell(1);
                titlecell1.setCellValue(operationlogs.get(i).getContent());
                HSSFCell titlecell2 = row.createCell(2);
                titlecell2.setCellValue(format.format(operationlogs.get(i).getCreatetime()));
            }
            System.out.println((start + 1) + "万条数据完成导出<<<");
        }

        response.setCharacterEncoding("utf-8");

        File file = null;

        switch (type) {
            case 5:
                file = new File(filePath+"Excel/划拨查询结果.xls");
                break;
            case 6:
                file = new File(filePath+"Excel/回收查询结果.xls");
                break;
            case 7:
                file = new File(filePath+"Excel/开卡查询结果.xls");
                break;
        }

        if (!file.exists()) {
            file.createNewFile();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
        long endtime = System.currentTimeMillis() - startTime;
        System.out.println("OK，导出完成，一共用时：" + endtime);
        /**
         * TODO 导出卡信息结束
         */
        System.out.println("totlenum:" + totlenum + "times:" + times);
        // TODO 转跳下载

        String basePath = request.getServerName() + ":" + request.getServerPort() + "/";

        // 服务器域名协议
        String headPath = null;
        switch (request.getScheme()) {
            case "http":
                headPath = "1";
                break;
            case "https":
                headPath = "2";
                break;
            default:
                headPath = "3";
        }

        // 数据库下载路径
        String fileName = null;
        switch (type) {
            case 5:
                fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/划拨查询结果.xls";
                break;
            case 6:
                fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/回收查询结果.xls";
                break;
            case 7:
                fileName = request.getServerName() + ":" + request.getServerPort() + "/file/Excel/开卡查询结果.xls";
                break;
        }
        file = new File(fileName);

        Map<String, String> map = new HashMap<>();
        map.put("fileName", fileName);
        map.put("headPath", headPath);
        JSONObject json = JSONObject.fromObject(map);
        out.print(json);
    }
}